Excel MODE.MULT Function

If we want to get the most occurring numbers in an excel set in a vertical array, we can deploy the excel MODE.MULT function. Here is an elaborative guide on how to go about it.

Figure 1: Using MODE.MULT function

General syntax of the formula

=MODE.MULT (number1, [number2], …)

Where;

• Number1– refers to the number or cell reference containing numeric values
• Number2– number or cell reference with numeric values, it’s optional

Understanding the formula

• This function will return a vertical array of those values that are most occurring in your dataset.
• One can supply the numbers in various forms, either as numbers, named ranges or even cell reference with the numeric values.
• This function can only accept arguments to a maximum of 254.
• The formula should be entered in an array form, i.e. Control + Shift + Enter
• You can use the TRANSPOSE function to get the values in a horizontal array. All you will need to do is to transpose the original vertical array. This can be done by the following formula; =TRANSPOSE(MODE.MULT(range))

Note

The MODE function will ignore empty cells, cells with text or those with Boolean values.

Also, the function will return a #N/A error if the supplied number do not have duplicates.

Here, we want to know those numbers that are most occurring. To do this, we proceed as follows;

Step 1: Fill your values in column D as shown above.

Step 2: Select the vertical cells you want to get your results in

Step 3: In cell E6, put the formula; =MODE.MULT (D6:D13)

Step 4: Press Control + Shift + Enter

Example

Figure 2: Finding most frequent values with excel MODE.MULT function

We can see in the above example, the formula results in 100 and 30 because those are the most frequent numbers of the dataset in column D.

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.