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.
