Exceljet

Quick, clean, and to the point

Excel MODE.SNGL Function

Excel MODE.SNGL function
Summary 

The Excel MODE.SNGL function returns the most frequently occurring number in a numeric data set. For example, =MODE.SNGL(1,2,4,4,5,5,5,6) returns 5.

Purpose 
Get most frequently occurring number
Return value 
A number representing the mode.
Syntax 
=MODE.SNGL (number1, [number2], ...)
Arguments 
  • number1 - A number or cell reference that refers to numeric values.
  • number2 - [optional] A number or cell reference that refers to numeric values.
Version 
Usage notes 

The MODE.SNGL function returns the most frequently occurring number in a set if numeric data. If supplied data does not contain any duplicate numbers, the MODE.SNGL function returns a #N/A error.

The MODE.SNGL function takes multiple arguments in the form number1, number2, number3, etc. Arguments can be a hardcoded constant, a cell reference, or a range, in any combination. MODE.SNGL ignores empty cells, text values, and the logical values TRUE and FALSE. The MODE function will accept up to 254 separate arguments. 

Examples

In the example shown, the formula in D5 is:

=MODE.SNGL(B5:B14) // returns 95

MODE.SNGL returns the most frequently occurring number in supplied data. For example,

=MODE.SNGL(1,2,4,4,5,5,5,6) // returns 5
=MODE.SNGL(7,8,9,7,9) // returns 7

If there are no duplicate numbers, the MODE function returns the #N/A error:

=MODE.SNGL(7,9,6,5,3,1,0) // returns #N/A

Note: MODE will return a single value even when there are multiple modes, to return a list of all modes, see the MODE.MULT function

Notes

  • If supplied data does not contain duplicate numbers, MODE.SNGL returns #N/A
  • MODE.SNGL ignores empty cells, the logical values TRUE and FALSE, and text.
  • Arguments can be numbers, names, arrays, or references.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.