The coefficient of variation is one of the relative measures of dispersion.
The ratio between the standard deviation and the mean is known as the coefficient of variation. This is usually stated as a percentage.
Coefficient of variation = Standard deviation / Mean
The coefficient of variation tells you how much variability there is in your data set and how much change fluctuates compared to the mean.
When you have a low coefficient of variation value for a dataset, it indicates that the data set is less variable and more homogeneous, stable, and uniform.
On the other hand, a high coefficient of variation tells us that the data collection is less homogeneous, less stable, less uniform, and less consistent, as well as more variable.
If you work with statistical data, it could be useful to know how to calculate the Coefficient of Variation (CV) in Microsoft Excel.
There is no direct formula to find the coefficient of variation in Excel. You need to find the 2 parameters – Standard deviation and mean of the statistics formula separately to calculate this percentage.
Note: Excel allows you to calculate the standard deviation for a population as well as a sample (using the STDEV.P and STDEV.S functions, respectively). While the process to calculate the coefficient of variation for a population and a sample has the same steps, I’ve still shown these separately to make it easy for you to follow
Calculating the Coefficient of Variation of a Population
You have the final scores of 5 students for their final exam.
Let’s assume only these 5 students sat for the exam. Then the above data is related to a population.
Step 1 – Find the Mean
First, you need to find the average score using the below function.
=AVERAGE(B2:B6)
The syntax of the AVERAGE function is AVERAGE(number1,[number2],…).
You have to select at least one number for the AVERAGE function, and you can select cell ranges as well. Hence, we select all scores of students as a range.
Step 2 – Find the Standard Deviation
Then we have to find the standard deviation of the population.
The below formula helps to get the standard deviation of a population.
=STDEV.P($B$2:$B$6)
The syntax of the STDEV.P function is STDEV.P(number1,[number2],…).
You have to select at least one number for the STDEV.P function. This function supports arrays as well. So, we select all scores of students as an array for the first argument of the function.
The STDEV.P function calculates the standard deviation using the “N” method, and it uses the below statistics formula.
This function ignores empty cells, logical values, text, and error values in the array or reference. The STDEVPA function should be used if you want to factor such values into the calculation.
Step 3 – Find the Coefficient of Variation
Finally, to get the Coefficient of Variation, you have to divide the standard deviation of the population by the mean.
So, you have to enter the formula below in cell B10.
=B9/B8*100%
You can use the below formula if you wish to find the Coefficient of variation in one step.
=STDEV.P(B2:B6)/AVERAGE(B2:B6)*100%
Also read: How to Calculate Variance in Excel?
Calculating the Coefficient of Variation of a Sample
You have the final scores of 5 students for their final exam.
Let’s assume these 5 students are a sample of all the students who sat for the exam. Then the above data is related to a sample.
Step 1 – Find the Mean
First, you need to find the average score using the below function.
=AVERAGE(B2:B6)
The syntax of the AVERAGE function is AVERAGE(number1,[number2],…).
You have to select at least one number for the AVERAGE function, and you can select cell ranges as well. Hence, we select all scores of students as a range.
It is important to note that the average or the mean calculation for a sample and a population is the same.
Step 2 – Find the Standard Deviation
Then we have to find the standard deviation of the sample.
The below formula helps to get the standard deviation of a population.
=STDEV.S($B$2:$B$6)
The syntax of the STDEV.S function is STDEV.S(number1,[number2],…).
You have to select at least one number for the STDEV.S function. This function supports arrays as well, so, we select all scores of students as an array for the first argument of the function.
The STDEV.S function calculates the standard deviation using the “n-1” method, and it uses the below statistics formula.
This function ignores empty cells, logical values, text, and error values in the array or reference.
In case you want to include logical values or text representations of numbers in the calculation, you can use the STDEVA function.
Step 3 – Find the Coefficient of Variation
Finally, to get the Coefficient of Variation, you have to divide the standard deviation of the sample by the mean.
So, you have to enter the formula below in cell B10.
=B9/B8*100%
You can use the below formula if you wish to find the Coefficient of variation in one step.
=STDEV.S(B2:B6)/AVERAGE(B2:B6)*100%
In this lesson, you learned how to calculate coefficients of variation in Excel for a population as well as a data set in a sample.
You can calculate the mean and standard deviation individually and then divide the standard deviation by the mean, or you can use both formulas together to calculate the coefficient of variation in a single cell.
Other Excel articles you may also like:
- How to Calculate Standard Error in Excel
- How to Find Z-score in Excel?
- How to Calculate Covariance in Excel?
- Calculate Correlation Coefficient in Excel
- How to Get the p-Value in Excel?
- How to Calculate Confidence Interval in Excel
- How to Find Percentile in Excel (PERCENTILE Function)
- How to Find Slope in Excel (Easy Formula)
- How to Calculate Percentage Difference in Excel
- How to Calculate IRR with Excel (easy Examples)
- Weighted Average Formula In Excel
- How to Calculate Mean Squared Error (MSE) in Excel?
