Geomean in an Array Formula

[forevergrateful]

Hello All!

I'm trying to geomean the values of certain columns based on whether they
are within a particular date range. Here is the formula I've put together:

=GEOMEAN(IF(($A$37:$A$966>=$AW21)*($A$37:$A$966<=$AX21),BD$37:BD$966)^12-1)

$A$37:$A$966 -- The date range
$AW21 -- The begin date
$AX21 -- The end date
BD$37:BD$966 -- The values I want to geomean
)^12-1 -- annualizing the monthly values

I get a #Num error every time I try this. But if I separate the two parts
it works just fine. In other words, for one cell I do all but the
annualizing part. Then in another cell I look to that result and run the
^12-1 to annualize it. The problem is that it makes the whole thing a whole
lot bigger since I'm doing this on many date ranges.

What I'd like to know is why the geomean causes this? I do a very similar
thing all the time with Average and a way to find the percent that are
positive / negative and they all work.

Any help would be greatly appreciated.

FG

 

[Harlan Grove]

=GEOMEAN(IF(($A$37:$A$966>=$AW21)*($A$37:$A$966<=$AX21),BD$37:BD$966)^12-1)

$A$37:$A$966 -- The date range
$AW21 -- The begin date
$AX21 -- The end date
BD$37:BD$966 -- The values I want to geomean
)^12-1 -- annualizing the monthly values

....

I'm going to guess you want

=GEOMEAN(IF(($A$37:$A$966>=$AW21)*($A$37:$A$966<=$AX21),BD$37:BD$966))^12-1

instead.

 

[forevergrateful]

Harlan:

Wow!!!! I'd say that was some guess. I thought I had tried every
imaginable method but I must not have tried this since it worked right off.

Thanks a million!

FG