Formula compatibility issue can cause “Unknown function” error in Sheets. It often happens when you switch from other compatible Spreadsheet applications to Sheets. By including the MODE.SNGL function in Google Sheets, I think the Sheets’ development team is working to avoid compatibility errors in Sheets. Why did I think so?

Google Sheets has three functions to find the mode aka most occurring number in a dataset. They are MODE, MODE.MULT and MODE.SNGL. The function MODE.SNGL and MODE are actually similar in use. I don’t know whether the latter function will be deprecated in the future.

In this tutorial, you can learn how to use the MODE.SNGL function in Google Sheets. What about MODE.MULT?

Sometimes, your dataset may contain multiple model values (more than one mode). In that case, you can use the MODE.MULT function. If the dataset doesn’t contain any model value, then obviously the MODE.SNGL as well as the MODE.MULT function would return N/A error.

Syntax and Arguments of the MODE.SNGL Function in Google Sheets

Syntax of the MODE.SNGL Function:

MODE.SNGL(value1, [value2, …])

Function Arguments:

• value1 – The first value to consider when calculating a model value.
• value2 – The second value to consider when calculating a model value.
• value3, value4 … – Additional values to consider.

Example:

=mode.sngl(5,4,5)

In this, I have used three values – value1, value2, and value3. Also, you can refer to a range in Sheets MODE.SNGL function.

=mode.sngl(A1:A10)

Note:

Sheets MODE.SNGL function is specified as taking a maximum of thirty arguments. But, for this function, Sheets supports an arbitrary number of arguments.

MODE.SNGL – Formula Examples

To manually find the model value we must put the number in order and count the group. But when using the MODE.SNGL function, no need to SORT (arrange the numbers in order).

For example, see the numbers in column A which are sorted. The MODE.SNGL formula in cell C2 returns 18 as the model value (mode).

Count each group and you can see that the number 18 occurring thrice. The sorted data helps you manually find the model value.

I have copied the same numbers to column E and shuffled. The formula in cell G2 returns the same model value from this unsorted data.

=mode.sngl(E2:E12)

You May Like: How to Shuffle Rows in Google Sheets Without Plugin.

What happens when there are multiple modes and you use MODE.SNGL instead of MODE.MULT?

In such a case, the MODE.SNGL formula would return the first found most occurring value.

For the following number set;

5,4,11,16,18,18,18,20,20,20,25,26,27

The model value will be 18 and multiple model value will be 18 and 20.

MODE.SNGL Formula:

=mode.sngl(5,4,11,16,18,18,18,20,20,20,25,26,27)

Result: 18

MODE.MULT Formula:

=MODE.MULT(5,4,11,16,18,18,18,20,20,20,25,26,27)

The MODE.SNGL function arguments can be numbers, dates, time, and timestamp (DateTime). That means the function equally works in a date/time/DateTime range.

With this example, I am winding up this tutorial on the use of MODE.SNGL function in Google Sheets.

Cell C2 must be formatted to DateTime from the menu Format > Number > Date time.