where “data” is the named range B5:B16. The formula is then copied to rows below D5 to output the desired list of most frequent numbers. Note: this is an array formula and must be entered with control + shift + enter. Working from the inside out: In cell D5, no filtering occurs and the output of each step above looks like this: In cell D6, with 93 already in D5, the output looks like this:
Handling errors
The MODE function will return the #N/A error when there is no mode. As you copy the formula down into subsequent rows, you will likely run into the #N/A error. To trap this error and return an empty string ("") instead, you can use IFERROR like this:
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
