VBA Function help Geomean

[teatimecrumpet]

Hi,

I'm trying to build out a function that looks at two lists of numbers.
the two columns are numbers like prices. It then would go through and take the returns of one column taking a step at a time. If it is positive then I wanted to take the geomean of just those positive returns...but to avoid negative returns that would mess up the geometric mean it adds 1.

The below is what I have so far. I was trying to take the geometric mean in a roundabout way. But I can't get it working. For sums I usually use the red highlighted when I want to keep adding each step. But for multiplying it fails...because I think the first number starts as zero.

any help is appreciated!

Function geotest(r As Range, q As Range) As Double


Application.Volatile

Dim LR() As Double

RWS = r.Rows.Count

ReDim LJ(1 To (RWS - 1)) As Double


ReDim LR(1 To (RWS - 1)) As Double

For j = 2 To RWS And observ = 0

LJ(j - 1) = (r.Cells(j, 1).Value) / (r.Cells(j - 1, 1).Value) - 1
LR(j - 1) = (q.Cells(j, 1).Value) / (q.Cells(j - 1, 1).Value) - 1
If LJ(j - 1) > 0 _
Then bench = bench * (LJ(j - 1) + 1)
If LJ(j - 1) > 0 _
Then fund = fund * (LR(j - 1) + 1)

If LJ(j - 1) > 0 _
Then observ = observ + 1





Next j


benchgeo = bench ^ (1 / observ) - 1

fundgeo = fund ^ (1 / observ) - 1



geotest = fundgeo / benchgeo



End Function

 

[StephenCrump]

...but to avoid negative returns that would mess up the geometric mean ...

I'm not sure what you mean by this? The geometric mean will accommodate negative returns.

You can calculate the average more simply using the first and last values, e.g. if 100 increases to 120 over 5 periods, the average return is (120/100)^(1/5)-1 = 3.7% per period, i.e. regardless of the individual period returns, which may be positive/negative.

Just a couple of observations on your VBA:

- For a cumulative product, you need to initialise bench = 1 before your loop.

- You don't need Application.Volatile

- You need to reduce your loop to the size of the ranges. For example, r.Row will give you the start row for your range r, and r.Rows.Count will give you the number of rows.

 

[teatimecrumpet]

Hi Steve,

I thought I replied (though I'm having issues where I'm typing too fast for the board? and it'll miss every few keystrokes? crazy)

Thanks for the help! setting that variable to 1 beforehand solve everything.

For geomean I get a "#NUM!" error when there is a negative number. so it wouldn't work.

I throw in "Application.volatile" to make the formula refresh itself on open or when the range changes. Or at least that's what I think it does.

thanks again!

 

[StephenCrump]

Thanks for the feedback, I'm glad you got your function working.

You are right that GEOMEAN will return #NUM if any of the arguments are <0, but you shouldn't need to be using negative values. Let's take a simple example:

Suppose that an investment returns -15% and +20% over two successive time periods, and grows:

100 (say) --> 85 --> 102.

Your function calculates the (geometric) average return as the cumulative product (85/100 x 102/85)^(1/2)-1 = 0.995%.

The intermediate numerators and denominators cancel out, so you can simplify to (102/100)^(1/2)-1 as I noted previously.

Alternatively, GEOMEAN(.85, 1.2) - 1 = 0.995% per period.

You don't need the APPLICATION.VOLATILE because you've passed the ranges to the function as arguments. Change any value in the ranges, and the function will recalculate. Wheareas if you make the function volatile, it will recalculate (unnecessarily) any time calculation happens in the worksheet.