Standard Deviation malfunction when there is no data variation

Dear Forum Members!

I have encountered an issue (system glitch?) when trying to calculate Standard Deviation for a set of measurements that have no variation.

Raw measurements:

0.71000000000000000000000
0.71000000000000000000000
0.71000000000000000000000
0.71000000000000000000000
0.71000000000000000000000
0.71000000000000000000000
0.71000000000000000000000
0.71000000000000000000000
0.71000000000000000000000
0.71000000000000000000000
0.71000000000000000000000
0.71000000000000000000000
0.71000000000000000000000
0.71000000000000000000000
0.71000000000000000000000
0.71000000000000000000000
0.71000000000000000000000
0.71000000000000000000000
0.71000000000000000000000

Excel Office 365 calculation of Standard deviations (sample and population):

0.000000000000000333
0.000000000000000342

Can anyone please shed some light on this phenomenon? My expectation would be that Standard Deviation is 0 (zero).

There is a practical problem I am facing because of the above observed situation.

Best Regards,
Krzysztof

Doing some further research, the following emerges:

For the other values (e.g. 0.33), excel actually calculates standard deviation as 0 (zero):

sample #1 0.33
sample #2 0.33
sample #3 0.33
sample #4 0.33
sample #5 0.33
sample #6 0.33
sample #7 0.33
sample #8 0.33
sample #9 0.33
sample #10 0.33
sample #11 0.33
sample #12 0.33
sample #13 0.33
sample #14 0.33
sample #15 0.33
sample #16 0.33
st.dev. S 0.000000000000000000000000000000000000000000000

The same for measurement value of 1032:

sample #1 1032
sample #2 1032
sample #3 1032
sample #4 1032
sample #5 1032
sample #6 1032
sample #7 1032
sample #8 1032
sample #9 1032
sample #10 1032
sample #11 1032
sample #12 1032
sample #13 1032
sample #14 1032
sample #15 1032
sample #16 1032
sample #17 1032
sample #18 1032
sample #19 1032
st.dev. S 0.000000000000000000000000000000000000000000000

For 0.71, however, it calculates some residual number:

sample #1 0.71
sample #2 0.71
sample #3 0.71
sample #4 0.71
sample #5 0.71
sample #6 0.71
sample #7 0.71
sample #8 0.71
sample #9 0.71
sample #10 0.71
sample #11 0.71
sample #12 0.71
sample #13 0.71
sample #14 0.71
sample #15 0.71
sample #16 0.71
st.dev. S 0.000000000000000229326681863960000000000000000

st.dev.S formula used:

Formula:

Please Login or Register  to view this content.

Can this be atributed to the way that Excel performs rounding? Is there any 'fix' to this?

Any input would be greatly appreciated.

It looks like a result of floating point error -- which is part of most computer calculations: https://www.excelforum.com/groups/ma...nd-errors.html Because this is inherent in the way that computers do arithmetic, there is no "fix" for it. There are standards (such as the IEEE standard mentioned in the other links) to make the errors at least consistent across platforms, and Excel sometimes deviates from the standards (for reasons only Microsoft knows).

We as programmers need to be aware of these errors and our programming must take them into account. You mention that this non-zero result causes a problem, without giving specifics. Your programming will need some way of saying "if the result from the stdev.s function is within threshold of 0, then treat it as if it were exactly 0." There are multiple ways to do that (ROUND() functions are usually the first recommended in Excel), depending on exactly how this fits into your project.