Excel XP - using Var formula

  • Thread starter Thread starter Debra Lovelace
  • Start date Start date
D

Debra Lovelace

0.0015
0.0015
0.0015
0.0015

0.0015
4.24E-22

When calculating with the Variance formula, and using the
0.0015 number with a range greater than 4 cells, Excel
returns a value of 4.24E-22 vs. any other number with a
range greater than 4 cells, returns a value of 0.00E+00.
Why?
 
0.0015
0.0015
0.0015
0.0015

0.0015
4.24E-22

When calculating with the Variance formula, and using the
0.0015 number with a range greater than 4 cells, Excel
returns a value of 4.24E-22 vs. any other number with a
range greater than 4 cells, returns a value of 0.00E+00.
Why?
Click to expand...

Because Excel's VAR and VARP (and derivative STDEV and STDEVP) use a numerically
unstable algorithm to calculate variance. This is a long-standing known problem
in Excel (one Microsoft can't claim was required for compatibility with other
spreadsheets because Lotus 123 has always used a better, more stable algorithm
).

If you want to calculate sample variances, use

=DEVSQ(X)/(COUNT(X)-1)

If you want to calculate population variances, use

=DEVSQ(X)/COUNT(X)

[Jerry W. Lewis pointed these out to me a while ago.]
 
Back
Top