MODE.MULT Function in Excel – Multiple Modes

In the previous blog, we learned the basics of MODE.SNGL Formula. MODE.MULT Function in Excel works the same with one exception which is covered within the blog. Both the MODE.SNGL and MODE.MULT Formula is used for the same purpose.

So let’s begin the learning.

When to Use MODE.MULT Function in Excel?

MODE.MULT stands for “Multiple Modes”. The MODE.MULT Function in Excel returns the value that occurs most repetitively in the data set.

With the term “Multiple Mode” we mean that whenever there are multiple modes, the MODE.MULT Function will return multiple values. In other words, there can be a number of values that occur the maximum number of times in the data set.

MODE.MULT is an important Statistical Function. It is an array formula. This implies that the function can return multiple modes spilled in the range of cells.

Syntax and Arguments

Below is the information regarding inputs required by the MODE.MULT Function.

  • number1 – This inputs the numerical data set or value for which we want to find the mode. It can be a reference or direct numerical value.
  • [number2] – This argument is not mandatory to input.

There can be 255 maximum number input arguments in MODE.MULT Formula. Each number argument can be a cell reference, a range of cells, a named range, a hardcoded number or an array of values.

Important Points to Remember about MODE.MULT Function

There are the following notes that must be known to a person implementing the MODE.MULT Function.

  • The MODE.MULT Function came into existence in Excel 2010. For earlier versions, you can use the MODE function.
  • For Excel 2019 or an older version of Excel, the MODE.MULT Function needs special handling. MODE.MULT has the ability to return multiple modes in the form of an array. Consequently, MODE.MULT becomes an array formula.
  • This is a case for Excel 2019 or earlier versions. After entering the MODE.MULT Formula in any cell, we need to press the Ctrl Shift Enter key to get the spilled array of multiple mode values. In Excel 365, the function works as a dynamic array formula and we do not have to treat it as a CSE formula.
  • The Function returns a #N/A error when there are no repetitive values in the data set.
  • The MODE.MULT Function ignores empty cells, Logical Values or Text strings.
MODE.MULT Function in Excel – Multiple Modes

Examples to Apply MODE.MULT Function

In this section of the blog, we would execute some examples to learn the MODE.MULT Function Formula.

Example 1 – Passing Direct Numerical Values as the Function argument

Let us suppose we want to find the mode for the following set of numerical values without the usage of cell references.

{1,2,3,1,2,2,3,1,2}

We can either pass each of the numerical values as consecutive input or we can input an array containing these numerical values as a single input.

Use any of the two following MODE.MULT Formulas mentioned below.

=MODE.MULT(1,2,3,1,2,2,3,1,2)

or

=MODE.MULT({1,2,3,1,2,2,3,1,2})
MODE.MULT Function in excel passing direct numical valuesMODE.MULT Function in excel passing direct numical values

As a result, the formula returns the value of Mode as 2.

Explanation – We have passed the direct numerical values as the MODE.MULT number input. There were nine numerical values in the data set. We have passed them as number1,number2…..number9 arguments respectively.

Alternatively, we can supply the numerical data set in the form of an array of values contained in brackets as the number1 argument.

In both cases, the function returns the maximum occurring term as 2.

Example 2 – Passing Cell Ranges as Numerical Data Set

In this example, let us suppose we have the weights of different students in a class as follows.

MODE.MULT Function in excel as an array function

Type the following MODE.MULT Function and press Ctrl Shift Enter key.

=MODE.MULT(A2:A18)
concept of multiple modes in excel

As a result, the formula has spilled its results and returned two modes for the weights of 45 and 47 kgs.

Explanation – We have passed the range containing weights as the MODE.MULT formula input. The function interpreted that both the weights 45 and 47 kgs are occurring equal and maximum of 3 times. This is why, the formula resulted in multiple modes.

This brings us to the end of MODE.MULT Function blog.

Thank you for coming.

Leave a Comment