# How to find the most common number in Excel

Most common number in my spreadsheetFrédéric LE GUEN3 years agoReading Time: 3 minutesIn this article you will see how easy it is with Excel to find the most common number; only on

## Most common number in my spreadsheetFrédéric LE GUEN3 years agoReading Time: 3 minutes

In this article you will see how easy it is with Excel to find the most common number; only one function needed

## Return the most common number

You have collected the following document with the result of the same exam for 4 classroom. Of course, you can use the average or standard deviation to analyse the result.

But you can also immediately return the most common number with the function MODE.SNGL or formerly MODE.

=MODE.SNGL(B2:E11)

Here, no secret at all. **In statistics, the function MODE returns the most common number**, so, let's use it

## How many time do you have the most common number?

So now, we want to know how many time, the value returns by the function MODE.SNGL, is in our document.

Very simply, we are going to use the function COUNTIFS, to calculate this value

=COUNTIFS(B2:E11;MODE.SNGL(B2:E11))

## MODE.SNGL or MODE

In 2010, Microsoft has collaborated with statisticians to update some statistical functions like MODE, VAR (variance), STDDEV (standard deviation), ...

If you have a huge data-set of values **(more than 10,000 cells**), it makes sense to use the **new functions**.

But if you have hundreds or one or two thousands cells,** you can use both functions**. It won't impact the performance of your document.

## MODE is not MOD

Remark: In Excel, there is the function MODE but also the function MOD.

The function MOD (modulo) is very useful when you want to create specific logical tests. In the following example, we have build a test to check if a number is odd or even.

## Problem with percentages

When you have a document with percentages, you could have a problem to find the most common percentage

In the following example, three cells return 18% but the function **MODE.SNGL returns #N/A**, why? **It's because the functions has found only single values**

We have 3 times 18% so why the function MODE consider all values as unique? In fact, if you display more decimals of the percentage numbers you can see that all the values are different.

So, in this situation, if you want to return the most common percentage number, you must rounded the percentage value with the function ROUND.

With this trick, now you can now collect the most common number.

Categories: StatisticsTags: COUNTIFS, MODELeave a Comment