Standard deviation incorrect in Excel 97 SR2

  • Thread starter Thread starter Terri S
  • Start date Start date
T

Terri S

Hi,
I am building a sheet using standard deviation. When I use this calcuation
it gives me an incorrect answer. It seems to only happen with some 9 digit
numbers (all the same) in 16 cells. When the numbers are all the same, you
should get an answer of 0. The number I am using is 166829011. This
doesn't happen with all numbers. It gives an incorrect answer when you use
sixteen cells of nine 5's. Anyone have any ideas why this happens?

Thanks
Terri
 
Terri S wrote...
I am building a sheet using standard deviation. When I use this
calcuation it gives me an incorrect answer. It seems to only
happen with some 9 digit numbers (all the same) in 16 cells.
When the numbers are all the same, you should get an answer
of 0. The number I am using is 166829011. This doesn't
happen with all numbers. It gives an incorrect answer when
you use sixteen cells of nine 5's. Anyone have any ideas why
this happens?
Click to expand...

This happens because Excel 97 (actually all versions through Excel
2002) uses a fragile algorithm for calculating variance. In a nutshell,
VARP(X) is calculated as AVERAGE(X^2)-AVERAGE(X)^2 rather than as
AVERAGE((X-AVERAGE(X))^2). While the two are mathematically equivalent,
the former can suffer from rounding and truncation error that the
latter mostly avoids.

You need to use DEVSQ to avoid this because DEVSQ uses the numerically
superior algorithm. So

VARP(X) == DEVSQ(X) / COUNT(X)

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

STDEVP(X) == (DEVSQ(X) / COUNT(X))^0.5

STDEV(X) == (DEVSQ(X) / (COUNT(X) - 1))^0.5

Credit to Jerry W. Lewis for teaching me about this.
 
Back
Top