MODE.SNGL Function

Returns the most frequently occurring number in a numeric data set

Over 2 million + professionals use CFI to learn accounting, financial analysis, modeling and more. Unlock the essentials of corporate finance with our free resources and get an exclusive sneak peek at the first module of each course. Start Free

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:

MODE.SNGL Function

The formula to use is:

MODE.SNGL Function - Example 1

We get the result below:

MODE.SNGL Function - Example 1a

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:

Article Sources

  1. MODE.SNGL Function

Excel Tutorial

To master the art of Excel, check out CFI’s Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

Launch CFI’s Excel Course now

to take your career to the next level and move up the ladder!

0 search results for ‘