In this article, you will see how easy it is to find the most common value with Excel.
Return the most common number.
You have collected the following document with the results of the same exam for 4 classrooms.
Usually, to analyze such a document, people use the average or standard deviation functions. However, few people know that you can also return the most common value with another Excel function.
To return the most common value you have the MODE.SNGL function or formerly MODE.
=MODE.SNGL(B2:E11)
How many time do you have the most common value in your Excel document?
So now, we want to know how many times the value returns by the MODE.SNGL function is in our document. Very simply, we are going to use the COUNTIFS function, to calculate this value
=COUNTIFS(B2:E11;MODE.SNGL(B2:E11))
MODE.SNGL or MODE, which one to use?
You have certainly noticed that when you write the word MODE in a cell, 3 functions are listed, and one with a question mark
In 2010, Microsoft collaborated with statisticians to update some statistical functions like MODE, VAR (variance), STDDEV (standard deviation), ... Those functions have been redesigned to work better and faster. But for compatibility reasons, the old functions have been kept but it's better to use the new one.
This is why you still see them in the list of Excel functions but shouldn't use them.
MODE vs MOD
Remark: In Excel, there is the MODE function but also the MOD function for modulo. They should not be confused.
- MOD is useful for returning the number of leftover items, for instance.
- and MODE is the most common value in a list of numbers
Most Common percentage is not EASY to return
When you have a document with percentages, you could have a problem finding the most common percentage. In the following example, three cells return 18% but MODE.SNGL returns #N/A. Why?
It's because the function has found only single values
We have 3 times 18% so why does the function MODE consider all values as unique? In fact, if you display more decimals of the percentage, 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 round the percentage value with the function ROUND.
With this trick, you can now collect the most common number with percentage
