Go Back

List the Most Frequently Occurring Numbers in Excel

In excel, it is possible to list the most occurring numbers based on four Excel functions that include the IF, MODE, MATCH and ISNUMBER functions. This article provides a guide on how we can use these functions to list the most frequently occurring numbers in excel.

Figure 1: How to get a list of most occurring numbers in Excel

General syntax of the formula

{=MODE(IF(1 – ISNUMBER(MATCH(data, exp_rng, 0)), data))}

Where;

Data- refers to the range where the numbers from which you want to find the most frequently occurring are located.

Understanding the formula

The formula above is critical when we want to get a list of most frequently occurring numbers in a given column. Note that this is an array formula, and thus if you want to get the correct result, you need to enter it using the array format, i.e. Control + Shift + Enter.

How the formula works

  • This formula has four functions, the IF, MODE, MATCH and ISNUMBER functions
  • Among these functions, the MODE function plays the most critical role of returning those numbers that have appeared the most number of times.
  • The other functions are also vital, though not as much as the MODE function.
  • They only help to create a filtered array for MODE to use for each of the rows.
  • In our example above, you should have noticed $C$3:C3, which is referred to as the expanding range. This is used to exclude those numbers that already have their results in $C$3:C3.

Understanding the functions

In the formula, we have the following functions and the purpose they serve;

  1. MATCH function- the purpose of this function is to check all the numbers in the range, “data” against the existing numbers in the expanding range $C$3:C3
  2. ISNUMBER- this function is used to convert matched values to TRUE and those that do not match to FALSE
  3. 1- NUMBER- this one is used to reverse the array. Also, the math outputs ones and zeros
  4. IF– this uses the array output of result of 1-NUMBER to filter values in the original list. It excludes the $C$3:C3
  5. MODE- this functions then returns the most frequent number in the specified array.

Handling errors

In the event that in the array supplied there non-recurring numbers, the MODE function will return a #N/A error. We can use the IFERROR function to remove this error and replace it with empty strings. The formula will thus look like the one below;

=IFERROR(MODE(IF(1-ISNUMBER(MATCH(data, $C$3:C3,0)),data)), “ ”)

Example

Figure 2: Example of a list of most frequently occurring numbers

In this example, we want to list the most occurring numbers. To do this, proceed as follows;

Step 1: Prepare the list as shown in column B.

Step 2: Indicate where you want to get the results, as shown in column D and E

Step 3: Select the cells where you want the results to appear.

Step 4: Enter the formula relative to the active cell

Step 5: Press Control + Shift + Enter to enter the formula in an array form.

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need to count the most frequently lended in states, per lender (ref: Lender Loan Tape Summary, cell B4), for #1-3 most frequently lended states. (cells G9:G11 of "Lender Loan Tape Summary" tab)
Solved by F. S. in 29 mins
I have some data in a column, is there any formula which will give me most occuring word from the column
Solved by G. F. in 21 mins
I have a list of concatenated numbers, repeats are highlighted. How do I list them from most frequent to least? Thanks
Solved by B. C. in 13 mins

Leave a Comment

avatar