How to Calculate Standard Dev without using the worksheet function in VBA

[dinotruck]

Hello i have been stuck on a question for quite some time now. The question tells us that we cannot use the worksheetfunctions but to use For Loops to calculate the standard deviation. We are trying to calculate the STDDEV of a chosen month between 1 to 8 as a form of integer in an input box. If anyone can assist me in telling me how to go by using For Loops, that would be great. Thanks

[大灰狼1976]

Hi dinotruck!
Welcome to vbax forum!
VBA can use standard deviation formula, And also can use variables.
Please refer to the attachment for a simple example.


--Okami

[Paul_Hossler]

Hello i have been stuck on a question for quite some time now. The question tells us that we cannot use the worksheetfunctions but to use For Loops to calculate the standard deviation. We are trying to calculate the STDDEV of a chosen month between 1 to 8 as a form of integer in an input box. If anyone can assist me in telling me how to go by using For Loops, that would be great. Thanks

Tbis isn't the best (most elegant) way, but if you're learning loops, it's the most straight forward





Option Explicit


Sub MyStdDev()
Dim i As Long
Dim dMean As Double, dTemp As Double, dStdDev As Double, dCount As Double


For i = 1 To 8
dCount = dCount + 1
dTemp = dTemp + Range("A" & i).Value
Next i

dMean = dTemp / dCount

dTemp = 0#

For i = 1 To 8
dTemp = dTemp + (Range("A" & i).Value - dMean) ^ 2
Next i

dStdDev = Sqr(dTemp / (dCount - 1))




MsgBox dStdDev


End Sub