How to find the Coefficient of Variation in Excel

The Coefficient of Variation (CoV) is a statistical measure of the relative variability of data points in a data series around the mean.

It is a handy comparative measure for evaluating the degree of variation between different datasets. It is widely used in statistics, and talking about the practical world, it is a part and parcel of data analysis and comparison (be it from any discipline).

If you’re yet wondering how to calculate CoV in Excel, stop thinking and get your free practice workbook for this guide here. I will walk you through the concept of CoV together with the easiest method to calculate it in Excel and tips on how to interpret it.

Stay tuned 🔗

Table of Contents

What is the Coefficient of Variation

The Coefficient of Variation (CoV) is a standardized measure of dispersion of data.

To talk of a dataset in a simple number, we calculate its mean (or average). To see the variability in a dataset, we calculate the standard deviation for it 💡

Standard deviation tells how close or far away different points within a dataset are from its mean.

Example

For example, say collate the marks of the students of a class in Mathematics out of a total of 100 marks 📚

To see how the class has performed on average, you’ll calculate the mean of the marks of all students which, let’s assume comes out as 60. Then, have all the students performed the same? Or some students have performed exceptionally well while others have performed exceptionally badly?

How far or close are the marks of individual students from the mean of the results (i.e., 60)? To know this, we calculate the Standard Deviation (SD) for this result which comes out to 30.

This means the marks of students lie within 60 plus or minus 30 (30 to 90).

Does it make sense till here? 💭

Now if you want to compare the average performance and standard deviation within the results of Mathematics to the results of English, how do you do that? The total marks for the test of English are 200, the mean is 150 and the SD is 60.

Can we compare the SD of 30 for Mathematics to the SD of English of 60? Looks like the variation in English’s test is double that of Math’s. But that is not the right analysis, this is only because the total marks for both the subjects vary 🤔

Here’s where CoV comes in. It standardizes the SD for all datasets by calculating them as a proportion of the mean for each dataset.

It is the ratio of the standard deviation to the mean, usually expressed as a percentage. The CoV provides a sense of how much variability there is about the mean of the dataset.

So, the CoV for Math is 30/60 = 50% and for English, it is 60/150= 40%. The comparison makes more sense now. Hope you now understand CoV and its importance 🥇

Formula

The formula for the Coefficient of Variation (CoV) is:

Click to copy

Very simply, CoV shows the standard deviation of a dataset as a proportion of the mean of that dataset.

If you want to see this proportion as a percentage, multiply it by 100 or eliminate the multiplication with 100 parts to see it as a decimal 👌

How to find the coefficient of variation in Excel

Enough of the theoretical discussion, let us now see a practical example of calculating CoV in Excel. And it’s super easy, believe me.

Here I have a dataset that represents a sample of ages of different people residing in XYZ Town 🧓

Data range of ages of people of a town

Let’s see the CoV of this dataset.

Step 1) Calculate the mean for this dataset by using the AVERAGE function per the following formula:

Click to copy
Mean value of Ages of people’s ages

Step 2) Calculate the standard deviation for this dataset by using the STDEV.S function as below.

Click to copy
Excel formula of SD

Excel offers two main functions to calculate standard deviation in Excel. The STDEV.S function is to be used when your dataset is a sample of the population. It is divided by n−1 to provide an unbiased estimate of the population standard deviation. Whereas, the STDEV.P function is to be used when your dataset represents the entire population. It divides by n as it considers the data as the complete set.

Kasper Langmann, co-founder of Spreadsheeto

We have used the STDEV.S function as we are working with sample data.

Step 3) Divide the Standard Deviation for this dataset by the Mean of this dataset.

Click to copy
CV value in Microsoft excel

This gives you a CoV of 0.524.

If you want it as a decimal number, the calculation steps end here 😎

But if you want to see it as a percentage instead:

Step 4) Select the cell containing the CoV.

Step 5) Go to the Home tab > Number group > Percentage icon.

Percentage icon

It will convert the decimal number into a percentage.

Decimal converted into percentage

This shows the data has a variation of 52.4%. That’s a big percentage 💪

Looks like there are people of almost all ages in this town and the variation in ages is big.

Interpreting Coefficient of variation in Excel

Calculating CoV in Excel is an easy job (as we just saw in the above section).

However, even after you’ve calculated CoV in Excel, it won’t be of much use unless you know how to interpret it to derive the right meaning out of it 👇

The interpretation of a low or high CoV depends on the context and the field of study, but here are some general guidelines:

  • Low CoV: Typically, a CoV of less than 20% is considered a lower CV. It indicates that the data points within a given dataset are closely clustered around the mean, showing low relative variability.
  • High CoV: Generally, a CoV greater than 30% is considered high. It indicates that the data points within a given dataset are widely dispersed around the mean, showing high relative variability. Such data points are called outliers/
  • Moderate CoV: A CoV between 20% and 30% is often considered moderate, indicating moderate variability.

But, must remember! Different fields might have varying standards for what constitutes a low or high CoV. For example, for financial markets, a CoV of 10% might be high for a stable bond but low for a volatile stock.

There’s no standard guideline for interpreting CoVs and the acceptable range of CoV will always depend on your field and the purpose of the analysis 🏃‍♀️

Conclusion

The Coefficient of Variation is a valuable statistical metric for assessing the relative variability in a given dataset.

CoV allows you to see the deviation within a data as a percentage of its mean, allowing meaningful comparisons across different datasets. Using MS Excel to calculate a CoV is straightforward, quick, and error-free.

We have seen how you can quickly calculate the mean, standard deviation, and the CoV for any dataset in Excel in under 2 minutes. Enjoyed learning this? If yes, do not forget to check out the following Excel tutorials by Spreadsheeto that’ll help you gain more statistical insights into your data.