Hi Mike,

Thanks for the reply, i've also tested the setting. However, agreeable with
McGimpsey's reply (after yours), the result is still not zero. Is ok...accept
McGimpsey's sharing on the limitation...just have to use the rounding feature.

Thanks thought

"Mike H" wrote:

Hi,

You may test, with 60 sets of values "1" in a column, the STDEV is "0".
However, when applying the same formula to a fraction value "0.05", 60 sets
in a column, the STDEV is "4.9E-17".

On my machine Excel calculates the standard deviation od 60 data points of
0.05 correctly as zero. It only appears as 4.9E-17 if the cell is formatted
as General. Format as number to get the correct answer.

Mike


"Jacky" wrote:

Hi,

i was reviewing my preset worksheet with the standard deviation function and
noticed that the out put result contains error.

Formula "=STDEV(K12:K111)" was set to to calculate standard deviation
between 0-100 sets of values in the column, where values can be whole numbers
or fractions, in respective columns. Formula was observed performing
perfectly with whole numbers, however, with fractions (or numbers with
decimal places), the output is invalid (in some sense).

You may test, with 60 sets of values "1" in a column, the STDEV is "0".
However, when applying the same formula to a fraction value "0.05", 60 sets
in a column, the STDEV is "4.9E-17". This is definitely a NO NO answer in
mathematics point of view. If you breakdown the calculation steps of a STDEV
formula "s2 = (ˆ‘(x-m)^2)/N" into multiple columns, it would be identified
that the culprit lies on the "ˆ‘(X-M)^2" formula, where x is the individual
value and m is the mean.

Example below demonstrates the breakdown of the column calculations:
x m N ˆ‘(x-m)2 s2 s
0.05 0.05 1 0 0 0
0.05 0.05 2 0 0 0
0.05 0.05 3 1.44E-34 4.81E-35 6.94E-18
0.05 0.05 4 0 0 0
0.05 0.05 5 0 0 0
0.05 0.05 6 2.89E-34 4.81E-35 6.94E-18
0.05 0.05 7 3.37E-34 4.81E-35 6.94E-18
:
:
0.05 0.05 58 1.01E-31 1.73E-33 4.16E-17
0.05 0.05 59 1.02E-31 1.73E-33 4.16E-17
0.05 0.05 60 1.42E-31 2.36E-33 4.86E-17

*Note that the ˆ‘(x-m)2 is calculated using array formula
"{=SUM(($A$2:A61-B61)^2)}", since mean is constantly changing.

Is this due to limitation of the excel formula (STDEV only works for whole
numbers)? Or is there any patch for this error?