Excel VBA -Custom function sumproduct

  • Thread starter Thread starter ppp698
  • Start date Start date
P

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
 
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
 
Back
Top