Standard deviation custom function

A

arvex

New Member
Joined
May 10, 2011
Messages
18
Hello!
I have this formula:
hni97cujo31ty0qzwgm7.jpg

and i need to make vba code

x = range 1
f = range 2

I have started:

Code: 
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! :)
 
Sort by date Sort by votes

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
I found solution :) Thanks Cindy Ellis

Code: 
Option Base 1
Option Explicit
Function WeightedSD(x, f)
    Dim xArray() As Double
    Dim fArray() As Double
    Dim sum_fsqrdifs As Double
    Dim xcount As Long, fcount As Long, i As Long, avgx As Double, sumf As Double, kopskaits As Long
 
    xcount = Application.WorksheetFunction.Count(x.Value)
    fcount = Application.WorksheetFunction.Count(f.Value)
 
    If xcount <> fcount Then
        WeightedSD = CVErr(xlErrNA)
        Exit Function
    End If
    ReDim xArray(xcount)
    ReDim fArray(xcount)
    kopskaits = Application.WorksheetFunction.SumProduct(x, f)
    sumf = Application.WorksheetFunction.Sum(f)
    avgx = kopskaits / sumf
   
    sum_fsqrdifs = 0
    For i = 1 To xcount
        xArray(i) = x.Cells(i, 1).Value
        fArray(i) = f.Cells(i, 1).Value
        If fArray(i) < 0 Then
            WeightedSD = CVErr(xlErrValue)
            Exit Function
        End If
 
        sum_fsqrdifs = sum_fsqrdifs + (fArray(i) * ((xArray(i) - avgx) ^ 2))
    Next i
    If sumf = 0 Then
        WeightedSD = CVErr(xlErrDiv0)
        Exit Function
    End If
    WeightedSD = (sum_fsqrdifs / sumf) ^ 0.5
 
End Function
Edited xavg :)
 
Last edited:
Upvote 0
Glad I could help even though I gave you the wrong answer! I should known better than to do math after midnight! :oops:
 
Upvote 0
Hi Arvex,

Is this a weighted standard deviation? If so, a quick google search will provide mulitple ways of calculating it in Excel or using vba.

AMAS
 
Upvote 0
You can do this via an array formula:

=SQRT(SUMPRODUCT(wi*(xi-SUMPRODUCT(xi, wi)/SUM(wi))^2) / ((COUNTIF(wi, ">0")-1) * SUM(wi)/COUNTIF(wi, ">0")))

Or build the formula in code to avoid a loop:

Code: 
Function WgtSD(x As Range, w As Range) As Variant
    ' UDF only!
    Dim dAvgX       As Double   ' weighted average of x
    Dim sX          As String
    Dim sW          As String
    Dim sFrm        As String
 
    With WorksheetFunction
        If (x.Rows.Count > 1 And x.Columns.Count > 1) Or _
           (w.Rows.Count > 1 And w.Columns.Count > 1) Or _
           x.Cells.Count <> w.Cells.Count Or _
           .Count(x) < x.Cells.Count Or _
           .Count(w) < w.Cells.Count Or _
           .Min(w) < 0 Or _
           .Max(w) = 0 Then
            WgtSD = CVErr(xlErrValue)
        Else
            sX = x.Address(False, False)
            sW = w.Address(False, False)
            sFrm = "=sqrt(sumproduct(" & sW & " * (" & sX & " - sumproduct(" & sX & ", " & sW & ")/sum(" & sW & "))^2)" & _
                   " / ((countif(" & sW & ", "">0"") - 1) * sum(" & sW & ") / countif(" & sW & ", "">0"")))"
'            Debug.Print sFrm
 
            WgtSD = Evaluate(sFrm)
        End If
    End With
End Function
 
Upvote 0
Wtd Std Dev calculating using E[X^2] - E[X]^2 :
Code: 
Public Function WtdStdDev(x As Range, f As Range) as Double
    Dim FirstRawMoment As Double, SecondRawMoment As Double, Variance As Double
    FirstRawMoment = WorksheetFunction.SumProduct(x, f)
    SecondRawMoment = WorksheetFunction.SumProduct(x, x, f)
    Variance = SecondRawMoment - FirstRawMoment ^ 2
    WtdStdDev = VBA.Sqr(Variance)
End Function
 
Upvote 0
That's a very neat formulation, but it doesn't work for me (the Variance is negative) on this dataset:

<TABLE style="WIDTH: 74pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=98 border=0 x:str><COLGROUP><COL style="WIDTH: 37pt" span=2 width=49><TBODY><TR style="HEIGHT: 12pt" height=16><TD class=xl30 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>wi</TD><TD class=xl30 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>xi</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num="5.1999999999999998E-2">0.052</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num>99</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num="0.33900000000000002">0.339</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num>101</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num="0.53100000000000003">0.531</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num>99</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num="0.152">0.152</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num>103</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num="0.79200000000000004">0.792</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num>97</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num="0.50800000000000001">0.508</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num>95</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num="0.52700000000000002">0.527</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num>104</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num="0.17499999999999999">0.175</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num>100</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num="0.75800000000000001">0.758</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num>105</TD></TR></TBODY></TABLE>

=WtdStdDev(xi, wi) returns #Value
 
Upvote 0
shg said:
That's a very neat formulation, but it doesn't work for me (the Variance is negative) on this dataset:

<TABLE style="WIDTH: 74pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=98 border=0 x:str><COLGROUP><COL style="WIDTH: 37pt" span=2 width=49><TBODY><TR style="HEIGHT: 12pt" height=16><TD class=xl30 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>wi</TD><TD class=xl30 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>xi</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num="5.1999999999999998E-2">0.052</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num>99</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num="0.33900000000000002">0.339</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num>101</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num="0.53100000000000003">0.531</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num>99</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num="0.152">0.152</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num>103</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num="0.79200000000000004">0.792</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num>97</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num="0.50800000000000001">0.508</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num>95</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num="0.52700000000000002">0.527</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num>104</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num="0.17499999999999999">0.175</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num>100</TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" align=right height=16 x:num="0.75800000000000001">0.758</TD><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right x:num>105</TD></TR></TBODY></TABLE>

=WtdStdDev(xi, wi) returns #Value
Click to expand...

Your probabilities need to add up to one [e.g. the expected value is not the sumproduct of those two columns].
 
Last edited:
Upvote 0
Actually, it is.
Define Ew(X) as the weighted average of X. And, for convenience Xw=Ew(X). So,
Xw = Ew(X) = Sum(X*w)/Sum(w)
Now, the weighted variance is Sum((X-Xw)^2*w)/Sum(w)
= Sum(X^2*w)/Sum(w) - 2*Xw*Sum(X*w)/Sum(w) + Xw^2*Sum(w)/Sum(w)
= Ew(X^2) - 2Xw^2 + Xw^2
= Ew(X^2) - (Ew(X))^2

Based on the above, the following functions are lightly tested. They are also closely dependent on Excel.
Code: 
Option Explicit

Function WeightedAvg(X, w)
    Dim XA As String: If TypeOf X Is Range Then XA = X.Address Else XA = X
    Dim wA As String: wA = w.Address
    WeightedAvg = Evaluate("=SUMPRODUCT(" & XA & "," & wA & ")/SUM(" & wA & ")")
    End Function
Function WeightedVar(X, w)
    WeightedVar = _
        WeightedAvg(X.Address & "^2", w) - WeightedAvg(X, w) ^ 2
    End Function
Function WeightedStdDev(X, w)
    WeightedStdDev = Sqr(WeightedVar(X, w))
    End Function
Use them in an Excel worksheet as:
=WeightedAvg(G2:G4,H2:H4)
=WeightedVar(G2:G4,H2:H4)
=WeightedStdDev(G2:G4,H2:H4)
Cindy Ellis said:
Except in this case, there are multiple "replicates" of each value of x, so the the terms inside the parentheses would be a bit more complicated...something like
E((f * X)^2) - (E(f*x))^2 . (I'm not sure on that...just at a glance).
It may calculate faster or be more accurate, but it's way past midnight here so my math neurons are asleep already...it may make more sense in the morning.
Click to expand...
 
Upvote 0
:pray:
Awesome
Tested on a couple of data sets...gives the same result as manually creating "w" replicates of each value.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Standard deviation of a subset in VBA
Replies
7
Views
280
SHW2022
S
K
  • Solved
Custom Formula with Specific Functions
Replies
8
Views
196
dreid1011
dreid1011
J
  • Solved
Calculating the standard deviation of two weighted arrays.
Replies
5
Views
444
StephenCrump
S
T
  • Solved
VBA
Replies
6
Views
242
twildone
T
E
  • Question
Estimating Spreadsheet-Summing
Replies
1
Views
186
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,216,128
Messages
6,129,033
Members
449,482
Latest member
al mugheen

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top