There is an extensive recent discussion of this phenomenon in the "Rounding
error in Stdev function result" thread of the worhsheet functions newsgroup
http://groups.google.com/group/micro...4a0544a0e6d1cc

The short answer is that most decimal fractions (including all three you
mention and 1.4434 which the other thread discusses) have no exact binary
representation (much as 1/3 has no exact decimal representation) and hence
must be approximated. When you do math with approximate inputs it shoud be
no surprise that the output is only approximate.

Given that, the accuracy of the approximate final result will vary with the
algorithm used.

The pre-2003 algorithm actually does give 0 for three identical values of
1.35 and will give the least possible error when all inputs are integers of
moderate size in a sample of moderate size. It was discarded for the newer
algorithm because when it goes wrong, its error is much larger than that of
the 2003 algorithm.

The old algoirthm is roughly equivalent to
=SQRT(ABS(SUMSQ(data)-SUM(data)^2/COUNT(data))/(COUNT(data)-1))
in that this seems to agree with the old algorithm whenever the old formula
is nonzero (oddly, this formula is nonzero for 1.35 even though the old
formula is zero).

The new algoritm is equivalent to
=SQRT(DEVSQ(data)/(COUNT(data)-1))

The old formula sums squares of big numbers then subtracts another big
number. Most of the available precision is taken up in representing those
big sums of squares, resulting in less precision for the result of the
subtraction. The new formula (see help for DEVSQ) first calculates the
average, then squares deviations from that average. Much more precision
survives the subtraction, hence the better worst-case behavior.

This also shows where the error must come in for your special case; try the
following formula (keep the outer parentheses!)
=(x-AVERAGE(x,x,x))
When AVERAGE(x,x,x) is not exactly identical to the identical numbers being
averaged, then STDEV will give a non-zero result. This can only happen
because the numbers are non-terminating binary fractions, and the sum of the
approximations is different than the approximation to the sum.

I prefer yet a third approach to the standard deviation calculation
http://groups.google.com/group/micro...6ee0c636ad016a
One or the other of Excel's approaches may do a little better for a specific
set of numbers, but the worst case properties for these updating algorithms
are much better than either of Excel's approaches. Moreover these updating
algorithms are guaranteed to recognize the situation where all input numbers
are identical, and return exactly the input number as the mean, and zero as
the standard deviation.

If you want to learn more about internal binary approximations to numbers,
you might find the functions at
http://groups.google.com/group/micro...06871cf92f8465
to be useful.

Jerry

"Kimo" wrote:

For some numbers (e.g. 1.35, 2.8, 11.73) the standard deviation of the three
same numbers do not result to 0. Why? I tried it on four different computers.