MODE.SNGL Function

What is the MODE.SNGL Function?

The MODE.SNGL is the new gen function introduced in Excel 2010 that returns the value with maximum appearances in a given dataset.

The earliest you might have heard of mode may have been in school. Calculating the mathematical function back then was easy based on pen and paper number crunching.

Fast forward 10-15 years, and we are now dealing with thousands of rows of data daily. Pen and paper won’t help much, and it would probably take the entire eight hours on your job to get a single result.

This is where the MODE.SNGL function comes in with its abilities to calculate the number making maximum occurrences in the given dataset.

The function was introduced as a successor to the MODE function in Excel 2010 version along with MODE.MULT, which has since been present in all the newer versions.

This article will guide you on the syntax for the function and how to use the process, along with a couple of examples.

Understanding the MODE.SNGL Function

The MODE.SNGL is categorized as a Statistical function that finds and returns the ‘number’ making maximum appearances by referencing a cell or range of cells.

We specifically mentioned numbers since the function does not accept text values and returns the #N/A error.

In simple terms, the mode is just the number that has the maximum number of duplicates in a dataset. If there are no duplicates in a dataset, the function will return the #N/A error.

For example, if you have the numbers as 1,1,1,2,2,1,1,3,3,1,1, then using the function, you will get the result equal to 1.

The numerical value 1 makes seven appearances, followed by two appearances, each by numbers 2 and 3.

The MODE function, replaced by the two newer functions, was later demoted to backward compatibility and can still be used in Excel. However, using the more recent versions is advisable as Microsoft may discontinue the compatibility function shortly.

MODE.SNGL Function Formula

The syntax for the function is:

=MODE.SNGL(number1,[number2]...)

where,

• number1 - (required) a number or reference to the cell containing a numerical value or range of +cells containing numbers
• number2 - (optional) a number or reference to the cell containing a numerical value or range of cells containing numbers

How to use the MODE.SNGL Function in Excel?

To use the function from the library, click on the Formulas tab > More Functions > Statistical > and select MODE.SNGL function from the drop-down menu.

This will open up the dialog box where you can either hardcode the numbers or directly input the cell reference as an argument.

For example, the numbers as 1,1,1,2,2,1,1,3,3,1,1 can be input which gives the result in the dialog box as illustrated below:

If you count the occurrences of number 1 in the given data, you will find that it occurs the most number of times, and hence we get the same result even when we click on Ok.

This method makes sense if you have just begun to become an Excel wizard. However, most pros in the business prefer using the function as a worksheet formula since you can manipulate the procedures to achieve better results.

Suppose you have the data as illustrated below:

We are using the formula =MODE.SNGL(B3:B13) in cell E6, we get the result as 1, which is the same as what we had earlier.

By directly referencing the entire range B3:B13 in the formula, we can quickly check what number makes maximum appearances in the given dataset.

MODE.SNGL Function Examples

There is at least one instance in your day-to-day life where you need to find the number-making maximum occurrences.

This could be the price for the same group of products, dates, and time values which surprisingly are also numerical values, or the product numbers for a diverse range of items in an Amazon fulfillment center.

In this section, we will see f examples of how you can leverage the power of MODE.SNGL to find the number with maximum occurrences.

Example 1

Suppose you manage an Amazon fulfillment center that stores Apple Inc products.

At the end of the day, after all the products are shipped to the respective customers, you have the data for iPhones as illustrated below:

Since we know that the function does not work on text strings, we will reference the unique product ids in the range C3:C1. The formula will be =MODE.SNGL(C3:C11), which gives the result:

The product number 1003 corresponds to the product, which is the iPhone 14. What if we wanted the text string corresponding to the unique product number? Can we get them?

Let’s find out.

You see, even though we cannot directly reference the text strings in the MODE.SNGL function, there’s a hack that you can use to find the text string with maximum occurrences.

Here, we will use the combination of INDEX MATCH along with our star function such that the formula becomes =INDEX(B3:B11, MODE.SNGL(MATCH(B3:B11, B3:B11,0))) to give the result:

Thus, the maximum number of products sold from your fulfillment center is the iPhone 14.

Example 2

One of my degenerate friends said he made 10X returns on his trade. I was surprised; he did not have a trading background.

I asked him how much capital he employed for the trade, to which he replied $100. As a result, means he made $1000 on his work.

What we don’t know IS that he initially employed $5000 in option contracts, and all he was left with was $10 before he made the 10X.

After that, I asked him to invest in WSOAlpha, which has preserved his capital and made him freaking 14+ annualized returns on investment.

We always advise people that returns don’t matter if you cannot preserve the original principal amount.

Well, returning to our example, suppose that you take several trades in the options contract as illustrated below:

To find at what price the contract was purchased a maximum number of times before it was sold back to the market, we will use the formula =MODE.SNGL(E3:E12), which gives the result:

The maximum number of times the buying price for the call option was $31.34, which is quite surprising that we even had a maximum recurrence value in our dataset.

If there are no recurring values, we get the formula's result as a #N/A error.

MODE vs. MODE.SNGL Function

Suppose you were exploring Excel and came across the MODE function. In that case, you must be wondering - Why was there even a need to introduce two newer parts if a traditional role already existed in Excel?

Excel wanted to diverge from the core function and return results differently. So the traditional MODE function was replaced by the MODE.SNGL and MODE.MULT, where each has unique characteristics.

However, the one that comes closest to the ‘now’ compatibility function is the MODE.SNGL.

The MODE function also returns the value with the maximum number of occurrences in a given dataset.

Since a newer function replaced it, you might find it in the compatibility section for backward compatibility.

The syntax for the function is:

=MODE(number1, [number2])

where,

• number1 - (required) a hardcoded number or reference to the cell containing a numerical value or a range of cells containing numbers
• number2 - (optional) a hardcoded number or reference to the cell containing a numerical value or a range of cells containing numbers

Let’s see an example to prove to you that MODE and MODE.SNGL returns the same results.

Suppose that you have the data as illustrated below:

We will use the formula =MODE(C2:C6) in cell C8, which gives us the result as 2. On the other hand, the formula =MODE.SNGL(C2:C6) also gives us a similar result equal to 2.

This proves that we get the same result even after using either of the two functions. However, it is always advisable to use the newer function since Excel may discontinue the ‘backward compatibility’ function shortly.

MODE.SNGL vs. MODE.MULT Function

If one of the newer functions is an exact duplicate of the older version, there has to be some uniqueness to the ‘other’ latest version, which in this case is the MODE.MULT function.

So far, we saw that the function could only return one of the maximum recurring values from the given dataset. However, what if we had two or more numbers making the maximum number of appearances in a given dataset?

The MODE. The MULT function returns a vertical array of recurring values, i.e., the ones making maximum appearances in the given dataset.

The syntax for the function is:

=MODE.MULT(number1, [number2])

where,

• number1 - (required) the first number or the range of numbers
• number2 - (optional) the second number or the range of numbers

We are using the MODE. The MULT function can be tricky, so we will solely focus on using the function in Excel.

Suppose that you have the data as illustrated below:

Firstly, we will select a range of cells in which we expect our maximum recurrences value. For us, this range is equal to G6:G8.

Next, we will input the formula =MODE.MULT(C3:C11) in cell G6 as illustrated below:

Finally, the last step will be to press the Ctrl + Shift + Enter key to get the result since this is an array formula.

As you can see, two unique product numbers make the maximum appearances in the given dataset.

Here too, we can use the combination of INDEX MATCH to get the text strings in column B. First; however, you need to follow the earlier mentioned steps and just need input the formula =INDEX(B3:B11, MODE.MULT(MATCH(C3:C11, C3:C11,0))) as:

After pressing the Ctrl + Shift + Enter key, we get the result:

The #N/A error in our result confirms that there are no additional maximum recurring values in the given dataset.

It is always wise to select a more extensive range to return the result rather than a smaller capacity and miss out on the potential maximum recurring values.

In this case, if we had used the MODE.SNGL function using the formula =INDEX(B3:B11,MODE.SNGL(MATCH(C3:C11,C3:C11,0))), we get the result:

Even though there are two numbers with maximum recurrences, we still get the value encountered first by the first, which is the ‘iPhone 14’ in the given dataset.

Researched and Authored by Akash Bagul | Linkedin

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• WORKDAY Function
• XIRR Function
• XOR Function
• YEAR Function
• YIELD Function