!44: Calculate the Coefficient of Variation in Excel

!±8± Calculate the Coefficient of Variation in Excel

MS Excel is a powerful tool for crunching statistics. You may want to learn how to calculate the coefficient of variation, which is simply the standard deviation divided by the mean. Here how:

1. First, isolate a range of cells who coefficient of variation you want to know. For this example, let's assume that the data who coefficient of variation you want to calculate is in cells A2:A20.

2. Calculate the standard deviation of this data, the formula for which in this case is: =STDEV(A2:A20). Place that formula in cell C1.

3. Now, calculate the mean (average) for the same range by using this formula: =AVERAGE(A2:A20). Place that formula in cell C2.

4. To get the coefficient of variation, type this formula into cell C3: =C1/C2.

Another of the most useful statistics functions that Excel can crunch is that of variance. The variance value tells you, in effect, how uniform a sample of values (either a sample or an entire population) is as a whole. The formula for variance expressed in textual form is:

The sum of the difference between each value in the sample and the mean of all values in the sample (squared), divided by one less than the sample size.

The good news is: you don't need to memorize this formula. Rather, Excel will figure it out for you. Note: some people say they want to "calculate the coefficient of variation" in Excel. In reality, what they are saying is that they want to calculate the variance in Excel.

There are actually four different functions in Excel used to calculate variance. The formula you should choose depends upon:

a. whether the data you have access to represents a sample or the entire population

b. whether you want Excel to include any logical values (TRUE/FALSE values) and any text that can be interpreted as a value that it finds in the range you specify

Here is how to choose which of the four functions for calculating variance in Excel:

1. Use =VAR() when you are basing your calculation on a sample and you want Excel to ignore logical (TRUE/FALSE) values and text.

2. Use =VARA() when you are basing your calculation on a sample and you want Excel to treat logical values as TRUE=1, FALSE=0 and to interpret text values as numbers. Note: this formula will cause an error if the range of cells you select contains text that cannot be interpreted as a number.

3. Use =VARP()when you are basing your calculation on the entire population and you want Excel to ignore logical (TRUE/FALSE) values and text.

4. Use =VARPA()when you are basing your calculation on the entire population and you want Excel to treat logical values as TRUE=1, FALSE=0 and to interpret text values as numbers. Note: this formula will cause an error if the range of cells you select contains text that cannot be interpreted as a number.

How does the variance formula actually look in action? Here's an example. Let's say that the set of data whose variance you want to calculate is in cells A2 to A15. And, assume you have determined that you want to use the VAR version of the formula (see above). Here is the formula you should use in this case:

=VAR(A2:15)

That's it! Excel will return the variance figure you are looking for.