In this quick tutorial, I am going to elaborate on the use of the statistical function Mode.Mult in Google Sheets.

To find the model value in Google Sheets, you can use the function Mode. The model value is the most common number in a dataset.

That means there is already one function in Google Docs Sheets to find the model value in a dataset which is Mode. Then what is the role of the Mode.Mult?

In this post, I have detailed the role of Google Sheets Mode.Mult function and included some additional tips.

Mode.Mult Function Syntax and Formula Examples in Google Sheets

Syntax:

MODE.MULT(value1, value2)

Example:

=mode.mult(5,4,5,2)

The above formula would return the #5 as the model value (most frequently occurring number).

The below Mode formula would also return the same output!

=mode(5,4,5,2)

The difference between Mode and Mode.Mult lies in the latter’s capability in returning multiple modes.

Mode.Mult – The Multiple Mode Function

Assume you have a data set with multiple model numbers. Then the Mode function won’t come in handy. To return multiple modes, you must use the Mode.Mult function.

Points to Note:

1. When using this function you must leave enough blank cells below the formula applying cell. Otherwise, the formula would sometimes return #REF! error.
2. The Mode.Mult function is an array formula function. So, unlike in Excel, no need to enter this formula as an array formula either by hitting the Ctrl+Shift+Enter shortcut or directly using the ArrayFormula function.
3. The function returns #N/A error if there is no mode, that means if all the values in the dataset only occur once.

How to Check the Presence of Multiple Modes

I want to test a dataset whether it contains multiple model numbers. In this case, I can use the IF and Count function with the Mode.Mult function as below.

=if(count(mode.mult(B2:B9))>1,"Multiple Modes","")

With this example, I am winding up this tutorial. Thanks for the stay and hope you have enjoyed!