## Excel Coefficient

# Calculate the Coefficient of Variation in Excel

**
PowerSuite 2011 has Now arrived...
**

This is the most powerful boot utility disk. When your computer crashes, you cannot enter Windows, or you want to work under boot environment, this is the

**NO. 1**choice.

**http://www.updatexp.com/powersuite2011**

-----------------------------------------------------------

Microsoft 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 is 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. Looking to
improve your MS Excel skills? Download a FREE Excel cheat sheet at:
www.Untangle-Your-Thoughts.com

**Grab a Copy of My Excel - Hands On Guide!**

**Microsoft Excel Functions And Formulas Guide**

**Discover more about using Functions and Formulas -
CLICK HERE**!

-------------------------------------------------------------------------------------------------

**
PowerSuite 2011 has Now arrived...
**

This is the most powerful boot utility disk. When your computer crashes, you cannot enter Windows, or you want to work under boot environment, this is the

**NO. 1**choice.

**http://www.updatexp.com/powersuite2011**

-------------------------------------------------------------------------------------------------

** My FREE Windows Newsletter! **

Claim **YOUR**
Fortnightly copy of my **FREE Windows
Newsletter** covering:

Windows
XP, Windows Vista, Windows 7 , Microsoft Office and Windows Live Services -
**Sign-up TODAY**!!!

Enjoy!

**Kind Regards**

**Marc Liron - Microsoft MVP
(2004-2010)
www.marcliron.com
www.twitter.com/marcliron
http://blog.marcliron.com**