Calculating Standard Deviation in VB

[drpkrupa]

I have eight data entry feilds and one calculate field on my access form.
Here are my eight data entry feilds.
1) Nominal
2) Upper Tolerance
3) lower tolerance
4) Result 1
5) Result 2
6) Result 3
7) Result 4
8) Result 5

I want a calculate the last feild as standard deviation. I found code which works but not 100% match with stdev function in excel.
Here is the example:
2.11,0.01,0.01,2.1206,2.1209,2.1200,2.1203,2.1197 - result i got is 0.0004241659
I used same example in excel and use StDev funcation and it return -0.000474342

I can not figure out the different. This is what i did so far.
Dim i As Integer
Dim k As Integer
Dim avg As Single, SumSq As Single
k = 5
Dim Arr(5) As Single
Dim Std_Dev As Single
avg =((2.1206+2.1209+2.1200+2.1203+2.1197)/5)
Arr(2.1206,2.1209,2.1200,2.1203,2.1197)

For i = 1 To k
SumSq = (Arr(i) - avg) ^ 2
SumSq = SumSq + (Arr(i) - avg) ^ 2
Next i
'StdDev = Sqr(SumSq / (k - 1))

StdDev = Round(Sqr(SumSq), 10)

Need help.

 

[Galaxiom]

Your function has an error.
The line SumSq = (Arr(i)- etc should not be there as it is resetting the variable to the square of the variance of the current Arr(i) on each pass.

You end up with twice the square of the variance on the last value instead of the array.

 

[drpkrupa]

I removed that line then it gave me result 0.0011619955 but excel gave me result 0.000474342

I uncomment line StdDev = Sqr(SumSq / (k - 1)) and comment out line
StdDev = Round(Sqr(SumSq), 10)

Now i got result 0.0005809978

 

[Galaxiom]

Actually I didn't notice the commented out part after the For loop.
You certainly can't leave out that part of the calculation.

Use Double on your variables to get more precision.
Single is only good to about three decimal places.

 

[drpkrupa]

I converted single to double and got result 0.0004743416 and excel return 0.000474342