In this article, you will see how easy it is with Excel to find the most common value.
Return the most common number
You have collected the following document with the result of the same exam for 4 classrooms.
Usually, to analyze such a document, people use the average or standard deviation functions. But few pepole knows 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 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, 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 to return 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
Related articles
- The MOD function
- Percentage of Increase or Decrease
- Basic Statistical function in Excel
- Valeur les plus fréquentes