Maruf Islam
In Excel, users calculate various Statistics properties to showcase data dispersion. For this reason, users try to calculate the Coefficient of Variance in Excel. Calculating Coefficient of Variance (CV) is easy using Excel’s STDEV.P or STDEV.S in built functions as well as typical Statistics formulas.
Let’s say we have a dataset considered as Population (Set) or Sample and we want to calculate the Coefficient of Variance (CV).
In this article, we demonstrate typical Statistics formula, as well as the STDEV.P, and STDEV.S functions to calculate the Coefficient of Variance in Excel.
Download Excel Workbook
What Is Coefficient of Variance?
In general, the Coefficient of Variance (CV) is referred to as the ratio between Standard Deviation (σ) and the Average or Mean (μ). It displays the extent of variability against the Average or Mean of a Population (Set) or Sample. So, there are 2 distinct formulas for Coefficient of Variance (CV). They are:
🔺Coefficient of Variance (CV) for Population or Set,
🔺Coefficient of Variance (CV) for Sample,
⏩ Here, the Standard Deviation for Population,
⏩ The Standard Deviation for Sample,
3 Easy Ways to Calculate Coefficient of Variance in Excel
If users follow the Statistics formula to calculate the Coefficient of Variance (CV), they first need to find the Standard Deviation for Population (σ) or Sample (S) and Average or Mean (μ). Alternatively, users can use the STDEV.P and STDEV.S to calculate Population and Sample variants of Standard Deviation calculation. Follow the below section for detailed calculation.
Method 1: Using Statistics Formula to Calculate Coefficient of Variance in Excel
Before calculating the Coefficient of Variance (CV) users need to set up the data to find the formula components. As we mentioned earlier, the Statistics formula for the Coefficient of Variance (CV) is
Coefficient of Variance for Population,
Or
Coefficient of Variance for Sample,
🔄 Setting Up Data
Users need to manually find the Coefficient of Variance (CV) formula components such as Mean (μ), Deviation (xi-μ), and the Sum of the Squared Deviation (∑(xi-μ)2) to be able to calculate the Coefficient of Variance (CV).
⏩ Calculating Mean (μ)
The first step of calculating the Coefficient of Variance is to calculate the Mean of the data. Use the AVERAGE function to calculate the Mean or Average of a given dataset. Use the below formula in any cell (i.e., C14).
=AVERAGE(C5:C13)⏩ Finding Deviation (xi-μ)
Afterward, users have to find the Deviation from the Mean (xi-μ). It’s the minus value of each entry (xi) to the Mean (μ) value. Type the below formula in the Deviation (i.e., Column D) cells.
=C5-$C$14⏩ Finding Sum of Squared Deviation ∑(xi-μ)2
Now, Square the Deviation values (xi-μ)2 and place the data in the adjacent cells (i.e., Column E). Then sum the squared values in cell E14. Just use the SUM function in the E14 cell to find the sum of the squared deviations.
=SUM(E5:E13)The SUM function provides the total value of Column E.
⏩ Calculating Standard Deviation (σ or S)
The Standard Deviation for Population (σ) has its own formula as
Standard Deviation for Population (Set),
So, calculating the Standard Deviation needs to be the formula applied in the G6 cell.
➤ Paste the below formula in the G6 cell to find the Standard Deviation (σ).
=SQRT(E14/COUNT(C5:C13))The SQRT function results in the square root value and the COUNT function returns the total entry numbers.
➤ Hit or press Enter to apply the formula and the Standard Deviation value appears in cell G6.
Again, use the Sample version of the Standard Deviation formula to find the Standard Deviation. The formula,
Standard Deviation for Sample,
➤ Type the following formula in cell H6 to display the Standard Deviation.
=SQRT(E14/(COUNT(C5:C13)-1)) 
➤ Use the Enter key to apply the formula in the H6 cell.
⏩ Calculating Coefficient of Variance (CV)
After finding all the necessary components such as Standard Deviation and Mean, divide these two-component (Standard Deviation/Mean) into a Percentage preformatted cell.
➤ Execute the following formula in cell G11 to find the Coefficient of Variance for Population (Set).
➤ Press the Enter key to apply the below formula in cell H11 to find the Coefficient of Variance for the Sample.
=H6/C14 
🔺 At last, the Coefficient of Variance for both variants gets displayed in cells G11 and H11 as you can see from the below screenshot.
Read More: How to Do Variance Analysis in Excel (With Quick Steps)
Similar Readings
- How to Calculate Pooled Variance in Excel (with Easy Steps)
- Calculate Portfolio Variance in Excel (3 Smart Approaches)
- How to Calculate Variance Percentage in Excel (3 Easy Methods)
Method 2: Calculating Coefficient of Variance (CV) Using STDEV.P and AVERAGE Functions
Excel offers multiple in-built functions to carry out various Statistics calculations. The STDEV.P function is one of them. It takes numbers as its arguments.
As we mentioned earlier that the Coefficient of Variance (CV) is the quotient of two components (i.e., Standard Deviation (σ) and Mean (μ)). The STDEV.P function finds the Standard Deviation (σ) for Population and the AVERAGE function results in the Mean (μ) or Average.
Step 1: Use the following formula in cell E6.
=STDEV.P(C5:C13)/AVERAGE(C5:C13)The STDEV.P function returns the Standard Deviance for Population and the AVERAGE function results in the average or the mean value.
Step 2: Hit the Enter key to apply the formula. Instantly, Excel displays the Coefficient of Variance (CV) in a Percentage preformatted cell.
Read More: How to Calculate Variance in Excel (Easy Guide)
Method 3: Using STDEV.S and AVERAGE Functions to Calculate Coefficient of Variance
Alternative to the STDEV.P function, Excel has STDEV.S for sample data to calculate the Standard Deviation (σ). Similar to the STDEV.P function, STDEV.S takes numbers as its arguments. The typical Coefficient of Deviance (CV) formula is the ratio between the Standard Deviation (σ) and the Mean (μ).
Step 1: Use the following formula in cell E6.
=STDEV.S(C5:C13)/AVERAGE(C5:C13)
Step 2: Now, use the Enter key to display the Coefficient of Deviance in cell E6.
Read More: How to Calculate Variance Using Pivot Table in Excel (with Easy Steps)
Conclusion
In this article, we demonstrate the typical statistics way along with the functions to calculate the coefficient of variance in Excel. Users can choose any of the methods to calculate the Coefficient of Variance as they please. Hope this article clarifies your understanding of the Coefficient of Variance and its calculation. Comment, if you have further inquiries or have anything to add.
