The Excel MODE Function

MODE & MODE.SNGL Functions

In Excel 2010, the MODE function has been replaced by the MODE.SNGL function.

Although it has been replaced, current versions of Excel still have the Mode function (stored within the list of compatibility functions), to allow compatibility with earlier versions of Excel.

However, the Mode function may not be available in future versions of Excel, so it is advised that you use the MODE.SNGL function if possible.

Related Functions:

AVERAGE Function
MEDIAN Function

Function Description

The Excel MODE function returns the statistical mode (the most frequently occurring value) of a list of supplied numbers. If there are 2 or more most frequently occurring values in the supplied data, the function returns the lowest of these values

The syntax of the function is:

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

where the number arguments are a set of one or more numeric values (or arrays of numeric values), for which you want to calculate the statistical mode.

Note:

In current versions of Excel (Excel 2007 and later), you can supply up to 255 number arguments to the Mode function, but in Excel 2003, the function can only accept up to 30 number arguments.
Text and logical values within a supplied number array are ignored by the Mode function.

Mode Function Examples

Example 1

The following spreadsheet shows the Excel Mode function, used to calculate the statistical mode of the set of values in cells A1-A6.

Formulas:

	A	B
1	1	=MODE( A1:A6 )
2	1
3	2
4	2
5	2
6	3

Results:

	A	B
1	1	2
2	1
3	2
4	2
5	2
6	3

Example 2

The following spreadsheet shows the Mode function, used to calculate the statistical mode of the set of values in cells A1-A10.

Note that in this case, there are two modes in the data.

Formulas:

	A	B
1	1	=MODE( A1:A10 )
2	1
3	2
4	2
5	3
6	3
7	3
8	4
9	4
10	4

Results:

	A	B
1	1	3	- Returns the lowest of the two Modes, 3 and 4
2	1
3	2
4	2
5	3
6	3
7	3
8	4
9	4
10	4

In the above case, where the data in column A of the above spreadsheet has two statistical modes (3 and 4), the Mode function returns the lowest of these two values.

For further details and examples of the Excel Mode function, see the Microsoft Office website.

Mode Function Errors

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

Common Errors

#NUM!	-	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. that is not part of an array) is non-numeric. (Note that non-numeric functions that are part of an array of values are ignored by the Mode function).

Return to the Excel Statistical Functions Page
Return to the List of All Built-In Excel Functions