"Biff" wrote:
"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.

The most probable explanation is that the values are
calculated and only appear to be the same. The true
underlying values are different.

That is certainly one possible explanation, but not necessarily
"the most probable". Based on another thread on the same
subject, I suspect the OP is entering the numbers as constants.
(I wonder if this question is part of a class assignment.)

Anyway, the answer is somewhat the same. Binary computers
generally cannot represent decimal fractions exactly. When
arithmetic is performed on these numbers, precision may be
lost.

(Also, before Excel 2003, STDEV() used an algorithm that
exacerbated the problem with limited binary precision.)

It is interesting to compare the results of STDEV() with the
following VBA functions:

Function mystdev(x As Double) As Double
' enter =mystdev(number) in spreadsheet
Dim avg As Double
Dim var As Double
avg = (x + x + x) / 3
var = ((x - avg) ^ 2 + (x - avg) ^ 2 + (x - avg) ^ 2) / 2
mystdev = Sqr(var)
End Function

Function mystdev2(x As Double) As Double
' enter =mystdev2(number) in spreadsheet
mystdev2 = mystdev3(x, x, 1)
End Function

Private Function mystdev3(x As Double, sum As Double, cnt As Integer) As
Double
' internal; do not use in spreadsheet
Dim avg As Double
Dim var As Double
If cnt < 3 Then
mystdev3 = mystdev3(x, sum + x, cnt + 1)
Else
avg = sum / 3
var = ((x - avg) ^ 2 + (x - avg) ^ 2 + (x - avg) ^ 2) / 2
mystdev3 = Sqr(var)
End If
End Function

For the OP's three examples (and others), mystdev() does
result in zero, whereas mystdev2() has the same result as
STDEV(). The most likely explanation is that compiled VBA
code for mystdev() takes advantage of internal registers that
have more precision for intermediate computation. mystdev2()
is written to prevent such optimizations.

You can check by selecting the cells and changing the
format to number and increasing the decimal place.

Specifically, format as Scientific with 14 decimal places.