Excel XP - using Var formula

[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?

 

[Harlan Grove]

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?

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