The MODE.SNGL function returns the most frequently occurring, or repetitive, value in an array or range of data.

  • This function was introduced in Excel 2010 and so is not available in earlier versions.
  • The MODE.SNGL function replaces the MODE function included in earlier versions of Excel.

Syntax

=MODE.SNGL(num_1,[num_2], … )

Arguments

Argument Description
num_1 The first number, cell reference, or range for which you want the mode
[num_2], … Optional. Additional numbers, cell references or ranges for which you want the mode, up to a maximum of 254

Examples

  A B C D
1 Data Data    
2 2 12    
3 3 6    
4 3 9    
5 5 13    
6 7 5    
7 7 15    
8        
9 Formula Result Notes
10 =MODE.SNGL(A2:A7) 3 Returns the lowest of the 2 modes 3 and 7
11 =MODE.SNGL(A2:A7,B2:B7) 3 Returns the lowest of the 3 modes 3, 5, and 7

Common Function Error(s)

Problem What went wrong
#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
#NUM! Occurs if there are no duplicates, i.e. no mode, within the supplied values

The MODE.SNGL function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. The three most common measures of central tendency are:

  • Average – the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those numbers.
  • Median – the middle number of a group of numbers; that is, half the numbers have values that are greater than the median, and half the numbers have values that are less than the median.
  • Mode – the most frequently occurring number in a group of numbers.

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.