How to Calculate Coefficient of Variance in Excel (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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 the Coefficient of Variance (CV) is easy using Excel’s STDEV.P or STDEV.S inbuilt functions as well as typical Statistics formulas.

Let’s say we have a dataset considered as a Population (Set) or Sample and we want to calculate the Coefficient of Variance (CV).

Dataset-Calculate Coefficient of Variance in Excel

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.


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

🔺Coefficient of Variance (CV) for Sample,

Coefficient of Variance

⏩ Here, the Standard Deviation for Population,

Standard Deviation for Population

⏩ The Standard Deviation for Sample,

Standard Deviation for Sample


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.


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,

Elaborated Coefficient of Variance

Or

Coefficient of Variance for Sample,

Elaborated 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.

Setting Up the Data-Calculate Coefficient of Variance in Excel


Calculating Standard Deviation (σ or S)

The Standard Deviation for Population (σ) has its own formula as

Standard Deviation for Population

Standard Deviation for Population (Set),

Standard Deviation for Sample

 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.

Standard Deviation-Calculate Coefficient of Variance in Excel

Hit or press Enter to apply the formula and the Standard Deviation value appears in cell G6.

Standard Deviation-Calculate Coefficient of Variance in Excel

Again, use the Sample version of the Standard Deviation formula to find the Standard Deviation. The formula,

Standard Deviation for Sample,

Standard Deviation for Sample

Type the following formula in cell H6 to display the Standard Deviation.

=SQRT(E14/(COUNT(C5:C13)-1))

Standard Deviation for Sample -Calculate Coefficient of Variance in Excel

Use the Enter key to apply the formula in the H6 cell.

Standard Deviation for Sample result


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

=G6/C14

Coefficient of Variance-Calculate Coefficient of Variance in Excel

Press the Enter key to apply the below formula in cell H11 to find the Coefficient of Variance for the Sample.

=H6/C14

Coefficient of Variance

🔺 At last, the Coefficient of Variance for both variants gets displayed in cells G11 and H11 as you can see from the below screenshot.

Coefficient of Variance-Calculate Coefficient of Variance in Excel

Read More: How to Do Variance Analysis in Excel


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.

STDEV.P function-Calculate Coefficient of Variance in Excel

Step 2: Hit the Enter key to apply the formula. Instantly, Excel displays the Coefficient of Variance (CV) in a Percentage preformatted cell.

STDEV.P result

Read More: How to Apply Variance Formula in Excel to Get Plus-Minus Results


3. Using STDEV.S and AVERAGE Functions to Calculate Coefficient of Variance in Excel

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)

STDEV.S function-Calculate Coefficient of Variance in Excel

Step 2: Now, use the Enter key to display the Coefficient of Deviance in cell E6.

STDEV.S function outcome

Read More: How to Find the Variance of a Probability Distribution in Excel


Download Excel Workbook


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo