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!

 

[arvex]

I worked on it 2 hours. Cant find solution. Need this formula working in vba by selecting 2 different ranges (like 2 same size columns or 2 same size rows).

 

[arvex]

Bump!

 

[Cindy Ellis]

I usually start out using a sub rather than a function because the code is easier to debug. Also, although I'm pretty good with statistics, and pretty good with VBA, I'm having a hard time figuring out what the formula is really trying to calculate.
That said...

• It's really confusing to use the names of operators and functions (e.g. minus and sum) as variable names. Try changing them to something that makes it clear that these are the results of a user-invoked calculation.
• The section with the "for each" loops is just setting the value of the variable to the result of the calculation for each a or b in turn, without storing or using the previous result. When you get to the "b" calculation, all of them are using the result for the final "a". That can't be right. Do you want to calculate the sum of the a's and the sum of the b's? Or do you want to use the result of the "a" calculation with the corresponding "b" calcuation?

Cindy

 

[Cindy Ellis]

I think I figured out that the input data represents a count or quantity of each given value of x, which makes the rest of the formula make sense to me (except that I would typically use n-1 in the denominator instead of n).
Here's how to calculate it in a sub...

Option Base 1
Sub SN()
    Dim xArray() As Double
    Dim fArray() As Double
    Dim sum_fsqrdifs As Double
 
    Set xRange = Range("A1:A5")
    Set fRange = Range("B1:B5")
    xcount = Application.WorksheetFunction.Count(xRange.Value)
    ReDim xArray(xcount)
    ReDim fArray(xcount)
    avgx = Application.WorksheetFunction.Average(xRange)
    sumf = Application.WorksheetFunction.Sum(fRange)
    sum_fsqrdifs = 0
    For i = 1 To xcount
        xArray(i) = xRange.Cells(i, 1).Value
        fArray(i) = fRange.Cells(i, 1).Value
        sum_fsqrdifs = sum_fsqrdifs + (fArray(i) * ((xArray(i) - avgx) ^ 2))
    Next i
    MyResult = (sum_fsqrdifs / sumf) ^ 0.5
    MsgBox MyResult
End Sub

...still working out details of converting it to a function.

Hope that helps,
Cindy

 

[Cindy Ellis]

This should do what you want...

Option Base 1
Function WeightedSD(x, f)
    Dim xArray() As Double
    Dim fArray() As Double
    Dim sum_fsqrdifs As Double
    
    xcount = Application.WorksheetFunction.Count(x.Value)
    ReDim xArray(xcount)
    ReDim fArray(xcount)
    avgx = Application.WorksheetFunction.Average(x)
    sumf = Application.WorksheetFunction.Sum(f)
    sum_fsqrdifs = 0
    For i = 1 To xcount
        xArray(i) = x.Cells(i, 1).Value
        fArray(i) = f.Cells(i, 1).Value
        sum_fsqrdifs = sum_fsqrdifs + (fArray(i) * ((xArray(i) - avgx) ^ 2))
    Next i
    WeightedSD = (sum_fsqrdifs / sumf) ^ 0.5
    
End Function

 

[Cindy Ellis]

One problem with the above code is that it doesn't trap for different size ranges for x and f. I'll have to figure that part out and post modified code...maybe in the morning.

 

[Cindy Ellis]

By the way...welcome to the board!

This checks for divide by 0, negative values for f, and different range sizes:

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
 
    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)
    avgx = Application.WorksheetFunction.Average(x)
    sumf = Application.WorksheetFunction.Sum(f)
    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

 

[mswoods1]

You could also calculate E(X^2) - E(X)^2. Might be faster.

 

[Cindy Ellis]

You could also calculate E(X^2) - E(X)^2. Might be faster.

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.