Function, Calculation of Standard Deviation of levels
 

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

Function, Calculation of Standard Deviation of levels
 

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

Perhaps one of you UDF's has an error.

--
Regards,
Tom Ogilvy


"David Murphy" wrote in message
...
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

Function, Calculation of Standard Deviation of levels
 

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


Tom Ogilvy wrote:

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

Perhaps one of you UDF's has an error.

--
Regards,
Tom Ogilvy

"David Murphy" wrote in message
...
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

Function, Calculation of Standard Deviation of levels
 

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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
if a UDF has an error, in xl97, it stops the calculation loop.

Perhaps one of you UDF's has an error.

--
Regards,
Tom Ogilvy


"David Murphy" wrote in message
...
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

Function, Calculation of Standard Deviation of levels
 

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

--
Regards,
Tom Ogilvy

"David Murphy" wrote in message
...
Thanks, Tom I'll look into it. When I return to worksheet and hit
recalculate
the correct numbers reappear.


Tom Ogilvy wrote:

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

Perhaps one of you UDF's has an error.

--
Regards,
Tom Ogilvy

"David Murphy" wrote in message
...
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

Function, Calculation of Standard Deviation of levels
 

Thanks, That seemes to do the trick.

Tom Ogilvy wrote:

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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
if a UDF has an error, in xl97, it stops the calculation loop.

Perhaps one of you UDF's has an error.

--
Regards,
Tom Ogilvy


"David Murphy" wrote in message
...
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

Function, Calculation of Standard Deviation of levels
 

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" wrote in message
...
Thanks, That seemes to do the trick.

Tom Ogilvy wrote:

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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
if a UDF has an error, in xl97, it stops the calculation loop.

Perhaps one of you UDF's has an error.

--
Regards,
Tom Ogilvy


"David Murphy" wrote in message
...
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

Function, Calculation of Standard Deviation of levels
 

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 wrote:

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" wrote in message
...
Thanks, That seemes to do the trick.

Tom Ogilvy wrote:

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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
if a UDF has an error, in xl97, it stops the calculation loop.

Perhaps one of you UDF's has an error.

--
Regards,
Tom Ogilvy


"David Murphy" wrote in message
...
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

Function, Calculation of Standard Deviation of levels
 

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" wrote in message
...
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 wrote:

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" wrote in message
...
Thanks, That seemes to do the trick.

Tom Ogilvy wrote:

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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
if a UDF has an error, in xl97, it stops the calculation loop.

Perhaps one of you UDF's has an error.

--
Regards,
Tom Ogilvy


"David Murphy" wrote in message
...
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

Function, Calculation of Standard Deviation of levels
 

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

Dana DeLouis wrote:

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" wrote in message
...
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 wrote:

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" wrote in message
...
Thanks, That seemes to do the trick.

Tom Ogilvy wrote:

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

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
if a UDF has an error, in xl97, it stops the calculation loop.

Perhaps one of you UDF's has an error.

--
Regards,
Tom Ogilvy


"David Murphy" wrote in message
...
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