MODE.SNGL Function

What is the MODE.SNGL Function?

The MODE.SNGL Function[1] is categorized under Excel Statistical functions. It will return the most frequently occurring number in a numeric data set. The function was introduced in MS Excel 2010 and is unavailable in earlier versions.

As a financial analyst, MODE.SNGL can be used to find out the most frequently occurring numbers in a data set. Suppose we wish to know the highest number of sales returns in the first quarter for the last five years, we can use the function to find the figures.

MODE.SNGL is different from MODE.MULT function as the MODE.SNGL function returns the lowest mode, whereas the MODE.MULT function returns an array of all the modes.

Formula

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

The MODE.SNGL function uses the following arguments:

1. Number1 (required argument) – The first number for which we want to calculate the mode.
2. Number2 (optional argument) – Number arguments between 2 to 254 for which we wish to calculate the mode.

We can use a single array or a reference to an array instead of arguments separated by commas. The function will return a single numerical value.

How to use the MODE.SNGL Function in Excel?

To understand the uses of the MODE.SNGL function, let’s consider an example:

Example

Suppose we are given the following data:

The formula to use is:

We get the result below:

Remember in the MODE.SNGL function, if there are two or more most frequently occurring values in the supplied data, then it will return the lowest of the values.

A few notes about the MODE.SNGL Function 

1. #N/A! error – Occurs when there are no duplicates in the values provided.
2. #VALUE! error – Occurs when the value provided to the function is non-numeric. Non-numeric functions that are part of an array of values are ignored by the MODE.SNGL function.
3. Arguments can either be numbers or names, arrays, or references that contain numbers.
4. If an array or reference argument contains text, logical values, or empty cells, the values are ignored; however, cells with the value zero are included.
5. Arguments that are error values or text that cannot be translated into numbers cause errors.
6. The MODE.SNGL function measures central tendency, which is the location of the center of a group of numbers in a statistical distribution.

Click here to download the sample Excel file

Additional Resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:

• Advanced Excel Formulas Course
• Advanced Excel Formulas You Must Know
• Excel Shortcuts for PC and Mac
• MODE.MULT Function