To calculate the coefficient of variation in Excel, you need to use a combination of STDEV.P and AVERAGE functions.
In this formula, STDEV.P will help you get the standard deviation, and the average function will help you to get the average of the data points.
In the end, you need to divide the standard deviation by the average to get the coefficient of variation.
Coefficient Variation is a measure of dispersion (variability) of data around its mean. The higher you have the coefficient of variation, the higher the level of dispersion that you have in the mean of data points.
You can also say it’s like dividing the standard deviation by the mean of the data.
In the following example, you have the following data with 7 different data points and now you need to calculate the CV (coefficient of variation) for these data points.
Steps to Calculate Coefficient of Variation in Excel
- First, enter STDEV.P in cell F1 and refer to the range A1 to E1 and close the function.
- After that, enter the divide operator.
- Next, enter the average function after that divide operator.
- Now, refer to the range A1 to E1.
- In the end, enter the closing parentheses and hit enter to get the result.
=STDEV.P(A1:E1)/AVERAGE(A1:E1)Note: You need to change the cell format to the percentage format.
To understand this formula, we need to split this formula into two parts, as I have said earlier. So, in the first part, we have the STDEV.P function which calculates the standard deviation by ignoring logical values and blank cells.
And in the second part, we have the average function that calculates the average of the data points.
In the end, when you divide 2.82842712474619 by 63 it gives you 0.04, and when you convert it to percentage format it is 4%.