Can geomean be used in an array formula?

Topic

New Reply

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_rodman_dam_date<=$B6)*ISNUMBER(OR_at_rodman_dam_discharge),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.

[TABLE="class: grid, width: 276"]
[TR]
[TD]Start Range
[/TD]
[TD]End Range[/TD]
[TD]Geomean
[/TD]
[/TR]
[TR]
[TD]8/1/1995
[/TD]
[TD]11/30/1995[/TD]
[TD]1443.12
[/TD]
[/TR]
[/TABLE]

Reply | Quote

Replies

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_rodman_dam_date0),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…

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

The problem was numeric overflow as you were multiplying 122 numbers together before taking the 122 root of those numbers

Reply | Quote