The Excel MODE.SNGL Function

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

Search this site:

Custom Search

Related Functions:

AVERAGE

MEDIAN

MODE.MULT

Basic Description

The Excel MODE.SNGL 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 Mode.Sngl function is new in Excel 2010 and so is not available in earlier versions of Excel. However, the function is simply a renamed version of the Mode function that is available in earlier versions of Excel.

The format of the Mode.Sngl function is :

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

where the number arguments are up to 255 numeric values, or arrays of numeric values, for which you want to calculate the mode.

Mode.Sngl Function Examples

The following example shows 3 examples of the Mode.Sngl function, used to calculate the mode of the values in cells A1 - A10. Although the same 10 values are used in each of the 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.Sngl Function

Results:

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

Trouble Shooting

If you get an error from the Excel Mode.Sngl 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.Sngl 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.Sngl 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