Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Posts
    398
    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.


  2. 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!

    Excel 2013: The Missing Manual

    + 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!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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; 2012-01-04 at 17:03.

  4. #3
    3 Star Lounger
    Join Date
    May 2002
    Posts
    398
    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.
    Attached Files Attached Files

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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 non-positive numbers in the dataset(negative and zeroes are both bad). Could you attach an example file where you get #num errors?

    Steve

  6. #5
    3 Star Lounger
    Join Date
    May 2002
    Posts
    398
    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.

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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

  8. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,089
    Thanks
    13
    Thanked 37 Times in 36 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.

  9. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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

  10. #9
    New Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •