Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA -Custom function sumproduct
Hi there,
I'm trying to develop a custom VBA function to calculate logarithmi growth rates. The function is pretty basic in nature; it consists o multiplying a log vector of observations (ex. sales) by a vector o time weights and dividing by the appropriate denominator. Given N observations, The vector of time weights equals (2*j - N -1), for j = 1 to N The denominator equals (N^3-N)/6 Here's the code I came up with to automate this process Function loggrowth(observations As Variant) As Variant Dim Denominator As Variant Dim j As Variant Dim Item As Variant Dim k As Variant Dim N As Variant N = Application.Count(observations) Denominator = (N ^ 3 - N) / 6 loggrowth = 0 j = 0 For Each Item In observations For j = 1 To N If WorksheetFunction.Ln(Item) 0 Then _ loggrowth = loggrowth + WorksheetFunction.Ln(Item) * (2 * j - N - 1) Next j Next Item loggrowth = loggrowth / Denominator End Function This doesn't seem to work for some reaso -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA -Custom function sumproduct
What does "This doesn't seem to work..." mean?
Irrespective of the answer to the above, you need to examine the weights. For any value of j < (N+1)/2 the corresponding weight will be <0! -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , ppp698 says... Hi there, I'm trying to develop a custom VBA function to calculate logarithmic growth rates. The function is pretty basic in nature; it consists of multiplying a log vector of observations (ex. sales) by a vector of time weights and dividing by the appropriate denominator. Given N observations, The vector of time weights equals (2*j - N -1), for j = 1 to N The denominator equals (N^3-N)/6 Here's the code I came up with to automate this process Function loggrowth(observations As Variant) As Variant Dim Denominator As Variant Dim j As Variant Dim Item As Variant Dim k As Variant Dim N As Variant N = Application.Count(observations) Denominator = (N ^ 3 - N) / 6 loggrowth = 0 j = 0 For Each Item In observations For j = 1 To N If WorksheetFunction.Ln(Item) 0 Then _ loggrowth = loggrowth + WorksheetFunction.Ln(Item) * (2 * j - N - 1) Next j Next Item loggrowth = loggrowth / Denominator End Function This doesn't seem to work for some reason --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
#VALUE! error with custom excel vba function | Excel Discussion (Misc queries) | |||
Excel custom function dialog | Excel Worksheet Functions | |||
Excel 2003: custom function with dotnet | Excel Programming | |||
Adding a custom function to the default excel function list | Excel Programming | |||
Excel Custom Function with Select Case | Excel Programming |