Standard deviation custom function

[arvex]

Hello!
I have this formula:

and i need to make vba code

x = range 1
f = range 2

I have started:

Function SN(x, f)
'------------------------------------------------

sum = Application.SumProduct(x, f)
avg = sum / Application.Sum(f)
fi = Application.Sum(f)
'------------------------------------------------
For Each a In x
minus = (a - avg) ^ 2
Next a

For Each b In f
xf = xf + minus * b
Next b

'------------------------------------------------

SN = (xf / fi) ^ 0.5
End Function

Something isnt right!
Need help!

 

[shg]

Consider this example:

<TABLE style="WIDTH: 356pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=474 border=0 x:str><COLGROUP><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 2230" span=3 width=49><COL style="WIDTH: 36pt; mso-width-source: userset; mso-width-alt: 2194" width=48><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 2230" span=2 width=49><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 694" width=15><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3803" span=2 width=83><TBODY><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: #f3f3f3" width=49 height=16>xi</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=49>wi</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=49> </TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 36pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=48> </TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=49> </TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 37pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #f3f3f3" width=49> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 11pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090" width=15> </TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #f3f3f3" width=83>STDEV</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #f3f3f3" width=83>STDEVP</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num>100</TD><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num x:fmla="=RANDBETWEEN(0,4)">3</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" align=right x:num x:fmla='=IF(COLUMNS($B1:C2)-2 < $B2, $A2, "")'>100</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" align=right x:num x:fmla='=IF(COLUMNS($B1:D2)-2 < $B2, $A2, "")'>100</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" align=right x:num x:fmla='=IF(COLUMNS($B1:E2)-2 < $B2, $A2, "")'>100</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" x:str="" x:fmla='=IF(COLUMNS($B1:F2)-2 < $B2, $A2, "")'> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #e7e7e7" align=right x:num x:fmla="=STDEV(C2:F7)">2.0000</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #e7e7e7" align=right x:num="1.8856180831641267" x:fmla="=STDEVP(C2:F7)">1.8856</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num>101</TD><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num x:fmla="=RANDBETWEEN(0,4)">3</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" align=right x:num x:fmla='=IF(COLUMNS($B2:C3)-2 < $B3, $A3, "")'>101</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" align=right x:num x:fmla='=IF(COLUMNS($B2:D3)-2 < $B3, $A3, "")'>101</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" align=right x:num x:fmla='=IF(COLUMNS($B2:E3)-2 < $B3, $A3, "")'>101</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" x:str="" x:fmla='=IF(COLUMNS($B2:F3)-2 < $B3, $A3, "")'> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #f3f3f3">StDevWgt(S)</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #f3f3f3">StDevWgt(P)</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num>102</TD><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num x:fmla="=RANDBETWEEN(0,4)">1</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" align=right x:num x:fmla='=IF(COLUMNS($B3:C4)-2 < $B4, $A4, "")'>102</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" x:str="" x:fmla='=IF(COLUMNS($B3:D4)-2 < $B4, $A4, "")'> </TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" x:str="" x:fmla='=IF(COLUMNS($B3:E4)-2 < $B4, $A4, "")'> </TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" x:str="" x:fmla='=IF(COLUMNS($B3:F4)-2 < $B4, $A4, "")'> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #e7e7e7" align=right x:num="2.1773242158070221">2.1773</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #e7e7e7" align=right x:num="1.8856180831639124">1.8856</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num>103</TD><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num x:fmla="=RANDBETWEEN(0,4)">0</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" x:str="" x:fmla='=IF(COLUMNS($B4:C5)-2 < $B5, $A5, "")'> </TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" x:str="" x:fmla='=IF(COLUMNS($B4:D5)-2 < $B5, $A5, "")'> </TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" x:str="" x:fmla='=IF(COLUMNS($B4:E5)-2 < $B5, $A5, "")'> </TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" x:str="" x:fmla='=IF(COLUMNS($B4:F5)-2 < $B5, $A5, "")'> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num>104</TD><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num x:fmla="=RANDBETWEEN(0,4)">0</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" x:str="" x:fmla='=IF(COLUMNS($B5:C6)-2 < $B6, $A6, "")'> </TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" x:str="" x:fmla='=IF(COLUMNS($B5:D6)-2 < $B6, $A6, "")'> </TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" x:str="" x:fmla='=IF(COLUMNS($B5:E6)-2 < $B6, $A6, "")'> </TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" x:str="" x:fmla='=IF(COLUMNS($B5:F6)-2 < $B6, $A6, "")'> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num>105</TD><TD class=xl31 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num x:fmla="=RANDBETWEEN(0,4)">2</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" align=right x:num x:fmla='=IF(COLUMNS($B6:C7)-2 < $B7, $A7, "")'>105</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" align=right x:num x:fmla='=IF(COLUMNS($B6:D7)-2 < $B7, $A7, "")'>105</TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" x:str="" x:fmla='=IF(COLUMNS($B6:E7)-2 < $B7, $A7, "")'> </TD><TD class=xl32 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #e7e7e7" x:str="" x:fmla='=IF(COLUMNS($B6:F7)-2 < $B7, $A7, "")'> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #909090"> </TD></TR></TBODY></TABLE>

The wi are random numbers, and the xi values are copied via formula. Both approaches always agree on the SD of the population.

For the SD of the sample, STDEV regards every member as a degree of freedom, but that's not true; the members are fixed; you can change the frequencies (weights), but not any of the individual numbers at the right.