What is the Most Common Value in your Excel document.

What is the Most Common Value in your Excel document.

Last Updated on 13/07/2023
Reading time: 3 minutes

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.

Example of exam of 4 classroom

To return the most common value you have the MODE.SNGL function or formerly MODE.

=MODE.SNGL(B2:E11)

Most common value returns by MODE.SNGL in Excel

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

Number of times the most common value is in the excel document

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

MODE or MODE.SNGL

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
The MOD function of Excel returns the number of leftover eggs

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?

Problem to return the most common value with percentage in Excel

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.

Percentage numbers with decimal

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

Percentage rounded with 2 decimals

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

Round percentage number

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *

What is the Most Common Value in your Excel document.

Reading time: 3 minutes
Last Updated on 13/07/2023

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.

Example of exam of 4 classroom

To return the most common value you have the MODE.SNGL function or formerly MODE.

=MODE.SNGL(B2:E11)

Most common value returns by MODE.SNGL in Excel

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

Number of times the most common value is in the excel document

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

MODE or MODE.SNGL

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
The MOD function of Excel returns the number of leftover eggs

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?

Problem to return the most common value with percentage in Excel

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.

Percentage numbers with decimal

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

Percentage rounded with 2 decimals

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

Round percentage number

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *