MODE.MULT function
MODE.MULT lists every value that occurs most often in a set of numbers and returns them as a vertical array. Unlike MODE.SNGL, which gives only one, it suits survey answers or categorical codes stored as numbers.
Example
Read it as a sentence: list the numbers that appear most often in A1:A8 and show the first one, which gives 4.
Syntax
MODE.MULT(number1, [number2], ...)
Arguments in [square brackets] are optional.
-
number1(required) - First number, array or range.
-
number2(optional) - More numbers or ranges.
Returns: A vertical array of every value that ties for most frequent.
One MODE.MULT formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=MODE.MULT({2,4,4,4,5,5,7,9}) | 4 | Array constant |
=MODE.MULT({1,1,2,2,3}) | 1 | Tie between modes |
=MODE.MULT({1;2;2;3;3;4}) | 2 | Vertical array, two modes (spill) |
=MODE.MULT(Data!A1:A8) | 4 | Range of numbers |
=MODE.MULT(Data!A1:A4,Data!F1:F5) | 4 | Two ranges |
=MODE.MULT(Data!AJ1:AJ5) | 3 | Range with one repeated value |
=MODE.MULT(10,"20",TRUE) | #N/A | Direct args: number, text-number, TRUE |
=MODE.MULT(10,"abc") | #N/A | Direct arg: non-numeric text |
The Data sheet these formulas read
A cell that starts with = holds that formula.
Watch out for
- Returns #N/A if no value repeats.
- Spills vertically in 365; older versions need Ctrl+Shift+Enter over several cells.
- Text and blanks in ranges are ignored.
Where it works
- Excel
- MODE.MULT arrived in Excel 2010. Older versions do not have it.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Statistical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .