Function, Calculation of Standard Deviation of levels

[David Murphy]

I have adapted an algorithm for calculating the standard deviation of
levels (20Log(x)) in an Excel function. The function seems to work
correctly but when I reopen the workbook or even open a chart with the
SD values the workbook fails to recalculate sometimes. I use XL97. The
code is provided below. Does anyone have any suggestions?

' This function calculates the positive 2 standard deviation for a
collection of ranges
' Created 6/22/04
' By: Dave Murphy
'
Function SDPDB(ParamArray Levels()) As Variant
Application.Volatile
Dim rng As Variant, S As Double, A As Double, Sig As Double
Dim SigPdB As Double, SigMdB As Double, x As Variant, n As Integer, LA
As Double
A = 0
S = 0
n = 0
For Each rng In Levels()
For Each x In Range(rng.Address)
If Not IsEmpty(x) Then
A = A + 10 ^ (x / 20)
S = S + 10 ^ (x / 10)
n = n + 1
End If
Next x
Next rng
A = A / n
LA = 20 * Application.WorksheetFunction.Log10(A)
Sig = 1 / (n - 1) ^ 0.5 * (S - n * 10 ^ (LA / 10)) ^ 0.5
SDPDB = 20 * Application.WorksheetFunction.Log10(1 + 2 * Sig / A)
End Function

Thanks for any help,
Dave

 

[Tom Ogilvy]

if a UDF has an error, in xl97, it stops the calculation loop.

Perhaps one of you UDF's has an error.

 

[David Murphy]

Thanks, Tom I'll look into it. When I return to worksheet and hit recalculate
the correct numbers reappear.

 

[Tom Ogilvy]

As an example,

For Each x In Range(rng.Address)

Range(rng.Address)
would refer to the activesheet when the calculation is performed (which may
not be the range intended)

You may have only one sheet in the workbook, but
why not
for each x in rng

 

[Tom Ogilvy]

See my followup comment - that makes it even more likely that what I said
may be happening.

 

[David Murphy]

Thanks, That seemes to do the trick.

As an example,

For Each x In Range(rng.Address)

Range(rng.Address)
would refer to the activesheet when the calculation is performed (which may
not be the range intended)

You may have only one sheet in the workbook, but
why not
for each x in rng

 

[Dana DeLouis]

Hello. Glad it works. Here's is just an idea. WorksheetFunction.Log10(x)
can be a little slow. Would this idea help? Hope I got it correct.

Replace these lines:

A = A / n
LA = 20 * Application.WorksheetFunction.Log10(A)
Sig = 1 / (n - 1) ^ 0.5 * (S - n * 10 ^ (LA / 10)) ^ 0.5
SDPDB = 20 * Application.WorksheetFunction.Log10(1 + 2 * Sig / A)

With this:

SDPDB = (20*Log(1+(2*n*Sqr(S-A^2/n))/(Sqr(n-1)*A)))/Log(10)

Again, just an idea.
HTH
Dana DeLouis

 

[David Murphy]

Dana,
Thanks for the thought. When I substitute your suggestion into my function I
get #VALUE! as a result. Your thought of Algebraically simplifying the math is
great but I haven't been able to so far.

Thanks again,

Dave

 

[Dana DeLouis]

Hello. I'm not sure what the problem could be. I just picked some random
data.
Here, both your version and my version return the same value. What test
data do you have?
21.5505928337063
21.5505928337063


Sub Demo()
Dim A, n, LA, Sig, SDPDB, S

'Test Data
A = 10: n = 5: S = 500

A = A / n
LA = 20 * Application.WorksheetFunction.Log10(A)
Sig = 1 / (n - 1) ^ 0.5 * (S - n * 10 ^ (LA / 10)) ^ 0.5
SDPDB = 20 * Application.WorksheetFunction.Log10(1 + 2 * Sig / A)
Debug.Print SDPDB


'Same Test Data
A = 10: n = 5: S = 500

SDPDB = (20*Log(1+(2*n*Sqr(S-A^2/n))/(Sqr(n-1)*A)))/Log(10)
Debug.Print SDPDB

End Sub

 

[David Murphy]

Dana,
I found my mistake. Your solution works great. Thanks for the help.
I'd be curious to see how you simplified the function since when I went back and
looked at it I didn't know where to start.
Dave