MODE.SNGL function
MODE.SNGL returns the number that appears most often in a set of numbers. If several values tie, it gives the one that appears first in the data.
Example
Read it as a sentence: find the single most frequent number in A1:A8, which gives 4.
Excel shows 4
A
1
2
2
4
3
4
4
4
5
5
6
5
7
7
8
9
Data
Syntax
MODE.SNGL(number1, [number2], ...)
Arguments in [square brackets] are optional.
-
number1(required) - First number, array or range.
-
number2(optional) - More numbers or ranges.
Returns: The most frequently occurring number.
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.SNGL({2,4,4,4,5,5,7,9}) | 4 | Array constant |
=MODE.SNGL({1,1,2,2,3}) | 1 | Tie between modes |
=MODE.SNGL(Data!A1:A8) | 4 | Range of numbers |
=MODE.SNGL(Data!A1:A4,Data!F1:F5) | 4 | Two ranges |
=MODE.SNGL(10,"20",TRUE) | #N/A | Direct args: number, text-number, TRUE |
=MODE.SNGL(10,"abc") | #N/A | Direct arg: non-numeric text |
=MODE.SNGL(5) | #N/A | Single number |
=MODE.SNGL({1,"2",TRUE,3}) | #N/A | Array with text and boolean |
The Data sheet these formulas read
A cell that starts with = holds that formula.
A
F
1
2
1
2
4
2
3
4
3
4
4
4
5
5
5
6
5
7
7
8
9
Data
Watch out for
- #N/A if every value is unique.
- Only works on numbers; for most common text use INDEX/MATCH with COUNTIF or a pivot.
Where it works
- Excel
- MODE.SNGL 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 .