You can use the following formula to find the 5 most frequent numbers in a list in Excel:
=MODE(IF(ISERROR(MATCH($A$2:$A$24,C$1:C1,0)),$A$2:$A$24))
This particular formula will find the most frequently occurring value in the range A2:A24.
You can then click and drag this formula down to four more cells to find the top 5 most frequent numbers in the range A2:A24.
The following example shows how to use this syntax in practice.
Example: Find 5 Most Frequent Numbers in Excel
Suppose we have the following list of numbers in Excel:
Suppose we would like to find the 5 most frequent numbers in this list.
To do so, we can type the following formula into cell C2:
=MODE(IF(ISERROR(MATCH($A$2:$A$24,C$1:C1,0)),$A$2:$A$24))
We can then click and drag this formula to four more cells in column C:
Column C now displays the five most frequently occurring numbers in column A.
To find the frequency of each number, we can then type the following formula into cell D2:
=COUNTIF($A$2:$A$24, C2)
We can then click and drag this formula down to each remaining cell in column D:
From the output we can see:
- The number 4 occurs most frequently in the list. It occurs 6 times.
- The number 15 occurs second most frequently in the list. It occurs 4 times.
- The number 5 occurs third most frequently in the list. It occurs 3 times.
And so on.
Additional Resources
The following tutorials offer additional information on how to count frequencies in Excel:
How to Create a Frequency Distribution in Excel
How to Calculate Relative Frequency in Excel
How to Calculate Cumulative Frequency in Excel
