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

#### LittlePucca

##### New Member
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!

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Hi LittlePucca,

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

Code:
``````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``````

Here's what I get:

Code:
``````       -----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.

My results:

Book1
AB
184,000770
2420,000180
31,050,00020
43,500,00020
510,500,0005
620,000,0005
7
8Average383,780
9Standard deviation1,646,484
Sheet1
Cell Formulas
RangeFormula
B8=SUMPRODUCT(A1:A6,B1:B6)/SUM(B1:B6)
B9=SQRT(SUMPRODUCT((A1:A6-B\$8)^2,B1:B6)/(SUM(B1:B6)-1))

shg,

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

Hi LittlePucca,

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

Code:
``````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``````

Replies
5
Views
230
Replies
2
Views
373
Replies
18
Views
510
Replies
4
Views
377
Replies
0
Views
273

### Forum statistics

1,207,436
Messages
6,078,542
Members
446,345
Latest member
MicCh ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back