Results 1 to 9 of 9

20120104, 11:05 #1
 Join Date
 May 2002
 Posts
 394
 Thanks
 1
 Thanked 0 Times in 0 Posts
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.

Subscribe to our Windows Secrets Newsletter  It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual  Subscribe and download Chapter 1 for free!

20120104, 16:08 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,168
 Thanks
 14
 Thanked 319 Times in 313 Posts
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...Last edited by sdckapr; 20120104 at 17:03.

20120105, 10:34 #3
 Join Date
 May 2002
 Posts
 394
 Thanks
 1
 Thanked 0 Times in 0 Posts
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.

20120105, 11:35 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,168
 Thanks
 14
 Thanked 319 Times in 313 Posts
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 nonpositive numbers in the dataset(negative and zeroes are both bad). Could you attach an example file where you get #num errors?
Steve

20120105, 12:36 #5
 Join Date
 May 2002
 Posts
 394
 Thanks
 1
 Thanked 0 Times in 0 Posts
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.

20120105, 12:50 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,168
 Thanks
 14
 Thanked 319 Times in 313 Posts
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

20120105, 13:11 #7
 Join Date
 Jan 2001
 Location
 La Jolla,CA
 Posts
 1,040
 Thanks
 11
 Thanked 35 Times in 34 Posts
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.

20120105, 18:14 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,168
 Thanks
 14
 Thanked 319 Times in 313 Posts
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

20120112, 16:45 #9
 Join Date
 Jan 2012
 Posts
 1
 Thanks
 0
 Thanked 0 Times in 0 Posts
The problem was numeric overflow as you were multiplying 122 numbers together before taking the 122 root of those numbers