MODE.SNGL Function in Excel – Single Mode

MODE.SNGL Function in Excel is a very important statistical function. Mode refers to the value that occurs most frequently in the numerical data set. The usage of MODE.SNGL Function is similar to MODE Function.

So let us begin learning the fundamentals of the function.

When to Use MODE.SNGL Function?

The word MODE.SNGL represents “Single Mode”. Mode refers to the value that occurs most frequently in the data set containing numbers.

The Single Mode concept is actually implemented when there are multiple numbers having an equal and maximum frequency in the data set. In this case, MODE.SNGL function returns the lowest mode value.

There can be one more situation, in which there is no repetition of values in the array. In this case, a # N/A error is returned as we can’t find the maximum frequency value until the values are repeated.

Syntax and Arguments

The following points contain information regarding the inputs required by the MODE.SNGL Formula in Excel.

  • number1 – This is a mandatory argument, We will input the numerical data set through this argument.
  • [number2] – This is an optional argument and works the same as the number1 argument.

We can supply a maximum of 255 such number arguments where each argument can be a number, a cell reference, a range or an array of values.

Points to Note about MODE.SNGL Formula

The following points are very important to remember while writing the MODE.SNGL Formula.

  • The Function was introduced in Excel 2010. To find Mode in earlier versions of Excel, you can use MODE Function.
  • If any array has text strings, logical values (TRUE / FALSE ) and empty cells, then they are ignored by the function.
  • If the function input has an error, then the function results in an error.
  • When there are no duplicate values, the MODE.SNGL function returns a #N/A error.
  • If there is a case where a number of values are occurring a maximum number of times, then you can use MODE.MULT Function.
MODE.SNGL Function in Excel – Single Mode

Examples to learn MODE.SNGL Function

In this section of the blog, we are going to implement the MODE.SNGL Formula.

Example 1 – Basic Example using Direct Values

In this example, we will assume any numerical data set containing duplicates as our data set.

1,2,3,4,5,6,7,7,7                          -  Numerical Data Set

We can take each number of data set as a separate MODE.SNGL Function number arguments. Alternatively, we can also pass the entire data set in the form of the array as a single number1 argument.

Use any of the following two MODE.SNGL Formulas



example to learn mode.sngl function in excel raw data

As a result, the function returns the mode of the data set as 7.

Explanation – We have passed the numerical values of the data set directly without referencing to any cell in the spreadsheet. As a result, the formula evaluates the mode as 7 as it is repeated 3 times.

Example 2 – Lowest Mode Concept – Single Mode Function

In many situations, there are different values that have the maximum frequency in the data set. Let us see how the formula works in this case.

We are taking the marks of students in a monthly test as follows.

example 2 to learn mode.sngl function in excel raw data

Above are the marks of 9 students in the range A2:B10. To find the mode of the data, use this formula.

example 2 to learn mode.sngl function in excel result

As a result, the formula returns the marks that are scored by most of the students as 10 marks.

Explanation – The data set has two marks with maximum frequency.

example 2 to learn mode.sngl function in excel explanation

Here both 10 and 11 occur three times. The MODE.SNGL Formula returns a single value of mode which is why it considers the lower value out of 10 and 11. Therefore, the mode for this data turned out to be 10 marks.

This brings us to the end of the MODE.SNGL Function blog.

Thank you for reading.

Leave a Comment