Excel VBA -Custom function sumproduct

[ppp698]

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

 

[Tushar Mehta]

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