| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Standard deviation calculation error... | Excel Discussion (Misc queries) | |||
| Which function to determine 1 and 2 standard deviation? | Excel Worksheet Functions | |||
| Standard deviation calculation | Excel Worksheet Functions | |||
| calculation of standard deviation is not as help document says | Excel Discussion (Misc queries) | |||
| Is there a standard deviation calculation in Excel? | Excel Worksheet Functions | |||
Linear Mode
