Sometimes, you may want to find the most common or frequent number in a range of cells in Excel. For example, you may want to know which product sells the most, which score appears the most, or which value occurs the most in a survey. In this article, we will show you how to use the MODE function to find the most common number in a range in Excel formula. We will also explain the basic theory behind the MODE function, the procedures to apply it, and some alternative approaches.
The MODE function is a built-in function in Excel that returns the most frequently occurring number in a range or array of numbers. The MODE function has the following syntax:
=MODE(number1, [number2], …)
where number1, number2, … are one or more numbers or references to numbers that you want to find the mode of. You can enter up to 255 arguments for the MODE function.
The MODE function will return the most common number in the arguments. If there are two or more numbers that have the same frequency and are the most common, the MODE function will return the smallest of them. If there is no mode, the MODE function will return the #N/A error.
Procedures
To use the MODE function to find the most common number in a range in Excel formula, you can follow these steps:
- Select a cell where you want to enter the formula.
- Type =MODE( and then select the range of cells that contain the numbers you want to find the mode of. Alternatively, you can type the range address manually, such as A1:A10.
- Close the parenthesis and press Enter. The MODE function will return the most common number in the range.
- If you want to find the second most common number, the third most common number, and so on, you can use an array formula that combines the MODE, IF, ISNUMBER, and MATCH functions. We will explain this formula in the next section.
Example
To illustrate how to use the MODE function to find the most common number in a range in Excel formula, let’s look at an example. Suppose we have a range of numbers in A1:A10 as shown below:
| A |
|---|
| 5 |
| 3 |
| 4 |
| 6 |
| 5 |
| 7 |
| 8 |
| 5 |
| 9 |
| 4 |
We want to find the most common number in this range. To do this, we can use the following formula in B1:
=MODE(A1:A10)
This will return 5, which is the most frequent number in the range.
If we want to find the second most common number, we can use the following array formula in B2:
{=MODE(IF(1-ISNUMBER(MATCH(A1:A10,$B$1:B1,0)),A1:A10))}
Note: this is an array formula and must be entered with Ctrl + Shift + Enter.
This formula will return 4, which is the second most frequent number in the range. The formula works by using the IF function to filter out the numbers that have already appeared in B1:B1, which is an expanding range. The ISNUMBER and MATCH functions are used to check if a number in A1:A10 matches any number in B1:B1. The 1-ISNUMBER operation reverses the array of TRUE and FALSE values to ones and zeros. The MODE function then returns the most common number among the filtered numbers.
We can copy the formula down to B3, B4, and so on, to get the third most common number, the fourth most common number, and so on. The results are shown below:
| A | B |
|---|---|
| 5 | 5 |
| 3 | 4 |
| 4 | 6 |
| 6 | 7 |
| 5 | 8 |
| 7 | 9 |
| 8 | 3 |
| 5 | #N/A |
| 9 | #N/A |
| 4 | #N/A |
We can see that the formula returns #N/A when there is no mode. To avoid this error, we can use the IFERROR function to return an empty string (“”) instead. For example, the formula in B2 can be modified as follows:
=IFERROR(MODE(IF(1-ISNUMBER(MATCH(A1:A10,$B$1:B1,0)),A1:A10)),“”)
Alternative Approaches
Besides using the MODE function, there are some other ways to find the most common number in a range in Excel formula. Here are some of them:
- You can use a pivot table to summarize the frequency of each number in the range, and then sort the pivot table by the frequency column in descending order. The most common number will be at the top of the pivot table.
- You can use the COUNTIF function to count the occurrence of each number in the range, and then use the MAX function to find the highest count. The most common number will be the one that has the highest count.
- You can use the FREQUENCY function to create a frequency distribution of the numbers in the range, and then use the MAX function to find the highest frequency. The most common number will be the one that has the highest frequency.
