How to write a loop to calculate standard deviation...

[LittlePucca]

Hi!

I have the data distributed as follow:

84,000 ---- repeats 770 times
420,000 ---- repeats 180 times
1,050,000 ---- repeats 20 times
3,500,000 ---- repeats 20 times
10,500,000 --- repeats 5 times
20,000,000 ---- repeats 5 times

This is the distribution in one cycle, I have 48 cycles with similar distribution. I want to write a Macros, that will basically calculate the average, and standard deviation for all the data in 1 cycle, 2 cycles, and 48 cycles. Can anybody help me? Thank you!

 

[xladept]

Hi LittlePucca,

With your sample, I got XBar=1013780 and Sigma=1268209.28946775

Sub GetSigma(): Dim m As Long, n As Long, r As Long, c As Long
Dim SumX As Double, XBar As Double, DXBar2 As Double, Sigma As Double
r = ActiveCell.Row: c = ActiveCell.Column
Do
m = m + 1
n = n + Cells(r, c + 1)
SumX = SumX + Cells(r, c) * Cells(r, c + 1)
r = r + 1
Loop Until Cells(r, c) = ""
XBar = SumX / n
r = ActiveCell.Row - 1: c = ActiveCell.Column
For m = m To 1 Step -1
DXBar2 = DXBar2 + (Cells(r + m, c) - XBar) ^ 2
Next m
Sigma = Sqr(DXBar2 / (n - 1))
End Sub

 

[shg]

Here's what I get:

       -----A------ -----B-----
   1      Value       Weight   
   2        84,000          770
   3       420,000          180
   4     1,050,000           20
   5     3,500,000           20
   6    10,500,000            5
   7    20,000,000            5
   8                           
   9   Weighted Avg    383,780 
  10   Weighted SD   1,645,660

B9: =SUMPRODUCT(A2:A7,B2:B7)/SUM(B2:B7)

B10: =SQRT(SUMPRODUCT(A2:A7, A2:A7, B2:B7)/SUM(B2:B7) - (SUMPRODUCT(A2:A7, B2:B7)/SUM(B2:B7))^2)

The results would be unchanged for any number of cycles.

 

[mjrofra]

My results:

Book1
	A	B
1	84,000	770
2	420,000	180
3	1,050,000	20
4	3,500,000	20
5	10,500,000	5
6	20,000,000	5
7
8	Average	383,780
9	Standard deviation	1,646,484
Sheet1

Cell Formulas
Range	Formula
B8	=SUMPRODUCT(A1:A6,B1:B6)/SUM(B1:B6)
B9	=SQRT(SUMPRODUCT((A1:A6-B$8)^2,B1:B6)/(SUM(B1:B6)-1))

 

[mjrofra]

shg,

I used n - 1 for sample. Without that correction I get same result as you.

 

[xladept]

Hi LittlePucca,

The other guys are right - I checked the figures and fixed my routine.

Sub GetSigma(): Dim m As Long, n As Long, r As Long, c As Long
Dim SumX As Double, XBar As Double, DXBar2 As Double, Sigma As Double
r = ActiveCell.row: c = ActiveCell.Column
Do
m = m + 1
n = n + Cells(r, c + 1)
SumX = SumX + Cells(r, c) * Cells(r, c + 1)
r = r + 1
Loop Until Cells(r, c) = ""
XBar = SumX / n
r = ActiveCell.row - 1: c = ActiveCell.Column
For m = m To 1 Step -1
DXBar2 = DXBar2 + (Cells(r + m, c) - XBar) ^ 2 * Cells(r + m, c + 1)
Next m
Sigma = Sqr(DXBar2 / (n - 1))
End Sub