# Can geomean be used in an array formula?

• 2012-01-04, 12:05
jepalmer
Can geomean be used in an array formula?
I use the following array formula to pull data by dates, where A6 is the beginning date and B6 is the ending date.

{=AVERAGE(IF((OR_at_rodman_dam_date>=\$A6)*(OR_at_r odman_dam_date<=\$B6)*ISNUMBER(OR_at_rodman_dam_dis charge),OR_at_rodman_dam_discharge))}

If I try to use geomean instead of average, I get ye olde #NUM! raspberry.

Is it possible to have a setup such as the following to calculate geomean? Thanks.

• 2012-01-04, 17:08
sdckapr
Yes, geomean can be used in an array. The formula you list does work with average replacing geomean. I suspect there is something about your dataset which leads to the #num error. Can you share a sample worksheet (no proprietary data) which demonstrates the issue?

Steve
PS. Just remembered. Geomean does NOT work with negative numbers and I checked and it will give the #num error. Try:
=GEOMEAN(IF((OR_at_rodman_dam_date>=\$A6)*(OR_at_ro dman_dam_date<=\$B6)*ISNUMBER(OR_at_rodman_dam_disc harge)*OR_at_rodman_dam_discharge>0),OR_at_rodman_ dam_discharge))

To ensure all numbers to calculate are positive.

PPS. If the negative numbers must be included then the dataset must be transformed in some way to work with a completely positive dataset. If financial numbers (eg) with positive an negative growths (+8%, +5, -3%, 0%, +12%, etc, you can transform to decimal multiplier (1.08, 1.05, 0.97, 1, 1.12, etc), calc the geomean of this, and then subract 1 from that value and format as percent...
• 2012-01-05, 11:34
jepalmer
geomean example
Data is all positive and > 0.
Example worksheet attached.

Could my problem be the difference between a colon and a comma; i.e., =average(a1:a10) vs =geomean(a1,a10) ?
see cells K3,L3 in attached.
• 2012-01-05, 12:35
sdckapr
1) I don't seen any formulas in your example which give the #num error, so am not sure what the original issue is.

2) Using a colon or comma has the same purpose in Geomean as in average. Average(A1:A10) is not the same as Average(A1, A10). The first is the average of 10 numbers, the 2nd is the average of only 2 (the first and the last). Geomean(A1,A10) is the Geomean of only 2 numbers, while Geomean(A1:A10) uses 100 values. Which one you use will depend on your intentions.

But regardless of whether you use a colon or comma, the only way I have found to get the #Num error is with non-positive numbers in the dataset(negative and zeroes are both bad). Could you attach an example file where you get #num errors?

Steve
• 2012-01-05, 13:36
jepalmer
The fact that you didn't see and #NUM! error made me check versions.

I'm using Excel 2007. I brought the spreadsheet into another computer with Excel 2010 and the formula works correctly. I checked on another computer with Excel 2007 and it doesn't work correctly there either.

So now my question is what is wrong with excel 2007? The help says =geomean(a1:a10) should work.

Thanks for the help.
• 2012-01-05, 13:50
sdckapr
I can't test with XL2007 since I have never had it. Perhaps it is a bug in XL2007. Instead of using the GEOMEAN function, you could calculate it indirectly (which mathematically is equivalent) using a formula like:

=EXP(AVERAGE(IF((Date>=\$A2)*(Date < =\$B2)*ISNUMBER(Discharge),LN(Discharge))))

Steve
• 2012-01-05, 14:11
kweaver
It might be that in 2007, the product of the numbers was used and there was an overflow. Seems MS may have fixed that in 2010.
When I used the ARRAY FORMULA: =EXP(AVERAGE(LN(J3:J124))) in 2007, it worked.
• 2012-01-05, 19:14
sdckapr
Perhaps. I have no idea of what the methodology used to solve it. If the procedure tried to take the nth root of the product of a large number of larger values, the product could have led to an overflow. A safer method is to take the average of the logs and then convert. They have have changed their methodology if it had issues in XL2007 especially with the larger number of possible values in the larger worksheets from the previous versions.

Glad to hear that the modification works.

Steve
• 2012-01-12, 17:45
rmandra
The problem was numeric overflow as you were multiplying 122 numbers together before taking the 122 root of those numbers