totn Excel Functions

MS Excel: How to use the MODE.SNGL Function (WS)

This Excel tutorial explains how to use the Excel MODE.SNGL function with syntax and examples.

Description

The Microsoft Excel MODE.SNGL function returns the most frequently occurring number found in a set of numbers. For Excel 2007 and older, use the MODE function instead.

The MODE.SNGL function is a built-in function in Excel that is categorized as a Statistical Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the MODE.SNGL function can be entered as part of a formula in a cell of a worksheet.

Microsoft Excel

If you want to follow along with this tutorial, download the example spreadsheet.

Download Example

Syntax

The syntax for the MODE.SNGL function in Microsoft Excel is:

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

Parameters or Arguments

number1, number2, ... number_n
Each number can be a range, a cell or a numeric value. There can be up to 255 numbers.

Returns

The MODE.SNGL function returns a numeric value.
If all numeric values in the set are unique, the MODE.SNGL function returns the #N/A error.
If a text value such as "M" is entered as a parameter, the MODE.SNGL function will return the #VALUE! error.
If a range or cell reference contains non-numeric values such as text values, logical values or empty cells, these values will be ignored.

Note

Applies To

  • Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010

Type of Function

  • Worksheet function (WS)

Example (as Worksheet Function)

Let's look at some Excel MODE.SNGL function examples and explore how to use the MODE.SNGL function as a worksheet function in Microsoft Excel:

Microsoft Excel

Based on the Excel spreadsheet above, the following MODE.SNGL examples would return:

=MODE.SNGL(B2:H2)
Result: 15

=MODE.SNGL(B2,C2,D2,E2,F2,G2,H2)
Result: 15

=MODE.SNGL(15,18,14,15,16,13,15)
Result: 15

=MODE.SNGL(B3:H3)
Result: 21

=MODE.SNGL(B4:H4)
Result: #N/A     'No repeating number is found

=MODE.SNGL(15,18,14,15,16,13,15,"M")
Result: #VALUE!  'Text literal "M" has been entered as a parameter

Combining Range and Value Arguments

The MODE.SNGL function can combine parameters that are both range arguments such as B2:H2 as well as value arguments such as the number 18 or the number 14.

For example, let's assume that the spreadsheet was missing four class sizes for Kindergarten - the values 18, 14, 18 and 18.

We could modify the formula to include these missing grades as follows:

=MODE.SNGL(B2:H2,18,14,18,18)
Result: 97

This formula would test for the most frequently occurring number in the range B2:H2 in addition to the values 18, 14, 18 and 18. In this example, the formula would return 18 since the number 18 appears four times in the set of numbers provided while 15 only appears three times.

Share on: