Close the Skill Gap -> Enroll to be a Certified Financial Modeling & Valuation Analyst (FMVA)® Today!

MODE.SNGL Function

Returns the most frequently occurring number in a numeric data set

What is the MODE.SNGL Function?

The MODE.SNGL Function is categorized under 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 2 or more most frequently occurring values in the supplied data, then it will return the lowest of the values.

 

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 resources:

  • Excel Functions for Finance
  • Advanced Excel Formulas Course
  • Advanced Excel Formulas You Must Know
  • Excel Shortcuts for PC and Mac

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE 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 Free Excel Course now to take your career to the next level and move up the ladder!