List most frequently occurring numbers
Have you been worried lately because making a list of the most frequently occurring numbers took a lot of your time? Well, you do not need to worry further as we are here to show you how to do it. Here is a simple way that how to list most frequently occurring numbers in excel.
You have to use this formula using Control+Shift+Enter keys as it is an array formula. Two curly brackets will be inserted automatically at the start and end of the formula as shown above.
If you want to list the numbers in their order of frequency as the most frequent, the second most frequent, the third most frequent, and so on, you need to use either of the four excel functions MODE, IF, MATCH, and ISNUMBER functions.
The Excel MODE function is an integral part of this formula as it brings the most frequently occurring numbers into an array. These four functions are used in this order:
- MATCH is used to check all the numbers in the list against already existing numbers in expanding range (exp_range)
- ISNUMBER transforms matched numbers to TRUE and the unmatched ones to FALSE
- 1- ISNUMBER causes a reversal of numbers in the array
- IF function filters the values which are there in the array
- MODE then arranges the list in the order of frequency
This stepwise method is quite effective to list most frequently occurring numbers.
Here is an example to better explain the process of listing the most frequently occurring numbers in Excel. Suppose you have a data set of numbers in a range of cells B5:B16 and you want to list most frequently occurring numbers found in the given range of numbers. Following formula will be used as an array formula in cell D5 and copy down to other rows as shown in the image below:
Figure 1. List most frequently occurring numbers in Excel by using MODE
When you copy down the above formula to subsequent rows the Excel MODE function will return the #N/A error value when there is no frequently occurring number is available in the array. To handle this error and return an empty string, you need to use Excel IFERROR function in the formula, such as:
You need to convert this formula into an array formula by using Ctrl+Shift+Enter.