Most common number in my spreadsheet

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.

Example of exam of 4 classroom

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

=MODE.SNGL(B2:E11)

Most common number returns by MODE.SNGL

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))

Number of times the most common number is in the document

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.

MOD function to test 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 😮😮

Problem to return the most common percentage

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.

Percentage numbers with decimal

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

Percentage rounded with 2 decimals

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

Round percentage number

Related posts


Permanent link to this article: https://www.excel-exercise.com/most-common-number-in-my-spreadsheet/

Leave a Reply

Your email address will not be published.