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
Yes you can modify my original function to be:

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) / WorksheetFunction.Sum(f)
    SecondRawMoment = WorksheetFunction.SumProduct(x, x, f) / WorksheetFunction.Sum(f)
    Variance = SecondRawMoment - FirstRawMoment ^ 2
    WtdStdDev = VBA.Sqr(Variance)
End Function
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
shg said:
Although ...

It gives a different answer than the NIST method (http://www.itl.nist.gov/div898/software/dataplot/refman2/ch2/weightsd.pdf). Perhaps NIST's is for a sample rather than a population?
Click to expand...

Right. Dividing by N-1 gives a non-biased sample variance. It is used when you don't know the true distribution of a variable but just have a sample of data points and you are trying to estimate the variance.

If you multiply the previous formulas by sqrt(N/[N-1]) I believe you get the same answer.
 
Last edited:
Upvote 0
Indeed, one fewer degrees of freedom, thank you. That's the (countif(...)-1)/countif(...) in my original formula.
 
Last edited:
Upvote 0
Interesting that "N" is the number of weights, not the count of the individual x's. This means that if you had individual values rather than summarized data, you would get a smaller standard deviation.
For the NIST data set at the above address, the weighted standard deviation per the N-1 adjusted formula (using count of unique x's instead of count of all x's) is 5.82, which corresponds to the NIST "correct" answer.
On the other hand, if you create a column with each x listed the number of times indicated by the weight, the sample standard deviation is 5.60, and the population standard deviation is 5.3, which matches the weighted standard deviation using either of the "final" formulas from Tusharm and mswoods1.
I wonder if the NIST formula assumes that there was some lost of data or precision by using frequency? Otherwise I don't see the point in only counting the number of unique x's.
 
Upvote 0
I think that is because the x values are only replicated by the weights, so the degrees of freedom are the number of non-zero weights (less one for a sample), not the number of x values.

Maybe.
 
Upvote 0
Here's the function I put in my library:
Code: 
Public Function StDevWgt(x As Range, w As Range, _
                         Optional bSample As Boolean = False) As Variant
    ' Returns the weighted standard deviation of a population (default)
    ' or a sample
    ' Credit to mswoods1 at [URL]http://www.mrexcel.com/forum/showthread.php?t=549040[/URL]
    
    ' UDF Only!

    Dim dSumW       As Double   ' sum of weights
    Dim dMom1       As Double   ' first moment
    Dim dMom2       As Double   ' second moment
    Dim dVar        As Double   ' variance
    Dim nW          As Long     ' number of non-zero weights
 
    With WorksheetFunction
        If .Min(w) < 0 Then
            StDevWgt = CVErr(xlErrValue)
        Else
            dSumW = .Sum(w)
            dMom1 = .SumProduct(x, w) / dSumW
            dMom2 = .SumProduct(x, x, w) / dSumW
            dVar = dMom2 - dMom1 ^ 2
            If bSample Then
                nW = .CountIf(w, ">0")
                StDevWgt = Sqr(dVar * nW / (nW - 1))
            Else
                StDevWgt = Sqr(dVar)
            End If
        End If
    End With
End Function
Thanks again, mwoods.
 
Upvote 0
In the original post, "f" was a count associated with a given value of x. If data is discrete rather than continuous, with f as the frequency, it seems odd to use N as the number of non-zero weights for calculating the sample (non-biased) std dev. If I have discrete data from 100 respondents, for instance, the data could be listed respondent-by-respondent for 100 rows, for which I can calculate either a population or sample standard deviation. Intuitively I expect to get the same population or sample standard deviation if I've chosen to compress 100 rows into a tally of the responses with a count (frequency) per response.
...just my thoughts...
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Custom Formula with Specific Functions
Replies
8
Views
167
dreid1011
dreid1011
J
  • Solved
Calculating the standard deviation of two weighted arrays.
Replies
5
Views
393
StephenCrump
S
T
  • Solved
VBA
Replies
6
Views
191
twildone
T
E
  • Question
Estimating Spreadsheet-Summing
Replies
1
Views
140
Peter_SSs
Peter_SSs
E
  • Solved
Summing Values
Replies
3
Views
216
LazyBug
LazyBug

Forum statistics

Threads
1,215,079
Messages
6,122,998
Members
449,092
Latest member
masterms

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