The Excel MODE Function

Home » Excel-Built-In-Functions » Excel-Statistical-Functions » Excel-Mode-Function

Search this site:

Custom Search

Related Functions:

AVERAGE Function

MEDIAN Function

MODE & MODE.SNGL Functions

In Excel 2010, the MODE function has been renamed as MODE.SNGL.

Although it has been replaced, the Mode function is still available in Excel 2010 (stored in the list of compatibility functions), to allow compatibility with earlier versions of Excel.

Basic 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 format 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 mode. In Excel 2007 and Excel 2010, 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 array are ignored by the function.

Mode Function Examples

The following example shows 3 examples of the Mode function, used to calculate the mode of the set of values in cells A1 - A10. Although the same 10 values are used in each of the functions in cells B1 - B3, they are supplied to the function in different ways in each case.

The format of the functions are shown in the spreadsheet on the left and the resulting values are shown in the spreadsheet on the right.

Formulas:

Examples of use of the Excel Mode Function

Results:

Further information and examples of the Excel Mode function can be found on the Microsoft Office website.

Trouble Shooting

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 an individual supplied value (i.e. not part of an array) is non-numeric.

Also, the following problem is encountered by some users:

Common Problem

The Mode function returns the wrong value, or returns the #VALUE! error, even though the supplied values all appear to be numeric.

Possible Reason

Text values, including text representations of numbers, are ignored by the Mode function. Therefore, this problem may arise if the values in the supplied array are text representations of numbers, instead of actual values (read more about Excel data types on the Excel Formatting page)

Solution

This problem can be solved by converting all array values into numeric values. To do this:

Use the mouse to select the cells you want to convert (this must not span more than one column)
From the Data tab at the top of your Excel workbook, select the Text to Columns ... option
Make sure the Delimited option is selected and click next
Make sure all the delimiter options are unselected and then click next again
You should now be offered a selection of Column Data Formats. Select General and click the Finish button

Note that the Text to Columns only converts values, it will not change the data type of a cell containing a function.

Return to the Excel Statistical Functions Page

Return to the List of All Built-In Excel Functions

Disclaimer

Privacy Policy