Calculating Standard Deviation

[drpkrupa via AccessMonster.com]

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.

 

[Tom van Stiphout]

On Wed, 17 Jun 2009 03:25:23 GMT, "drpkrupa via AccessMonster.com"

There is both a StdDev and a StdDevP function. Perhaps you have the
formulas confused?
Either way it is problematic to calculate stddev for such a small
population. I mean that the calculation will succeed, but the value is
pretty much meaningless.

-Tom.
Microsoft Access MVP

 

[Mike Painter]

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)

In the above the final value for sumsq will be two times (SumSq +
(Arr(k) - avg) ^ 2)
You start over again each time.


SumSq = 0
For i = 1 To k
SumSq = SumSq + (Arr(i) - avg) ^ 2
Next i

is probably what you want.

 

[drpkrupa via AccessMonster.com]

I add line StdDev = Sqr(SumSq / (k - 1)) and remove line
StdDev = Round(Sqr(SumSq), 10) and SumSq = (Arr(i) - avg) ^ 2

It gave me result 0.0005809978 but excel gave me result 0.000474342

For i = 1 To k
SumSq = (Arr(i) - avg) ^ 2

[quoted text clipped - 3 lines]

StdDev = Round(Sqr(SumSq), 10)

In the above the final value for sumsq will be two times (SumSq +
(Arr(k) - avg) ^ 2)
You start over again each time.

SumSq = 0
For i = 1 To k
SumSq = SumSq + (Arr(i) - avg) ^ 2
Next i

is probably what you want.