Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles Miscellaneous List the Most Frequently Occurring Numbers in Excel

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:

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc