Learn the Use of the MODE.SNGL Function in Google Sheets

Published on

Formula compatibility issues can cause a #NAME “Unknown function” error in Sheets, particularly when switching from other compatible spreadsheet applications to Sheets. By introducing the MODE.SNGL function in Google Sheets, it appears that the Sheets development team is working to mitigate compatibility errors.

Google Sheets offers three functions for finding the mode, or the most frequently occurring number, in a dataset: MODE, MODE.MULT, and MODE.SNGL.

The MODE.SNGL and MODE functions are essentially similar in use. However, it’s uncertain whether the latter function will be deprecated in the future.

In this tutorial, you’ll learn how to use the MODE.SNGL function in Google Sheets. But what about MODE.MULT?

Sometimes, your dataset may contain multiple mode values. In such cases, you can utilize the MODE.MULT function.

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

Syntax:

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

Function Arguments:

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

“value1” is necessary, whereas “value2” and subsequent arguments are optional.

MODE.SNGL: Formula Examples

Example 1:

=MODE.SNGL(5, 4, 5) // returns 5

In this example, three values (value1, value2, and value3) are used. Additionally, you can refer to range references in the MODE.SNGL function in Google Sheets.

Example 2:

=MODE.SNGL(A1:A10)

Note:

  • The MODE.SNGL function in Google Sheets is specified as taking a maximum of thirty arguments. However, Sheets supports an arbitrary number of arguments for this function.
  • If you specify ranges, they can be of the same size or different sizes. For example, value1 (range 1) could be A1:A10 and value2 (range 2) could be C1:C10 or C1:C50.

How Sorting Impacts MODE.SNGL Function Output

Sorting impacts the result because MODE.SNGL returns the first most frequently occurring number. When sorting your data ranges, it’s important to consider this.

Let’s see an example with both sorted and unsorted datasets:

The following formula utilizes an unsorted array as value1:

=MODE.SNGL({10; 4; 1; 5; 10; 5; 6}) // returns 10

Now, let’s use the same array with the values sorted:

=MODE.SNGL({1; 4; 5; 5; 6; 10 ; 10}) // returns 5

Additional Tip

The MODE.SNGL function arguments can include numbers, dates, times, and timestamps (DateTime). This means the function works equally well with date/time/DateTime ranges.

In the following example, value1 is the range A2:A, which contains timestamps. Please refer to the screenshot below for the formula and sample data.

MODE.SNGL in timestamp range

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

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.