The Excel MODE.MULT Function


The MODE.SNGL and the MODE.MULT functions both find the statistical mode (the most commonly occurring value (or values) of a supplied set of numbers).

The difference between the functions occurs when the supplied data set has more than one mode. The Mode.Sngl function returns the lowest of these values, whereas the Mode.Mult function returns an array of all of the modes.

Basic Description

The Excel MODE.MULT function returns a vertical array of the statistical modes (the most frequently occurring values) within a list of supplied numbers.

The function is new in Excel 2010 and so is not available in earlier versions of Excel.

The format of the Mode.Mult function is :

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

where the number arguments are up to 254 numeric values, or arrays of numeric values, for which you want to calculate the mode.

As the Mode.Mult function returns an array of values, it must be entered as an array formula. If the function is not entered as an array formula, only the first mode is returned, which is the same as using the MODE.SNGL function.

Array Formulas:
To input an array formula, you need to first highlight the range of cells for the function result. Type your function into the first cell of the range, and press CTRL-SHIFT-Enter.

Go to the Excel Array Formulas page for more details.
Example of use of the Excel Mode.Mult Function

Mode.Mult Function Example

Cells B1 - B2 of the spreadsheet on the right shows the Mode.Mult function, used to return the modes of the values in cells A1 - A10.

Clearly the data set has two modes, the values 3 and 5, and so the Mode.Mult function will return a vertical array containing these two values. It must therefore be entered as an array formula, into the two vertical cells B1 - B2.

The curly brackets seen around the function in the formula bar, show that it function has been entered as an array formula.

Further information and examples of the Excel Mode.Mult function can be found on the Microsoft Office website.

Mode.Mult Function Errors

If you get an error from the Excel Mode.Mult function this is likely to be one of the following:

Common Errors
#N/A! - Occurs if there are no duplicates (and there is therefore no mode) within the supplied values
#VALUE! - Occurs if a value that is supplied directly to the function (i.e. not part of an array of supplied values) is non-numeric.

Return to the Excel Statistical Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2015