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;

**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**ISNUMBER-**this function is used to convert matched values to TRUE and those that do not match to FALSE**1- NUMBER-**this one is used to reverse the array. Also, the math outputs ones and zeros**IF**– this uses the array output of result of 1-NUMBER to filter values in the original list. It excludes the $C$3:C3**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.

## Leave a Comment