GEOMEAN
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: GEOMEAN

  1. #1
    Guest

    Default

     
    Has anyone used this function with a large volume of data? I am trying to get the geometric average of 376 data points to compare with the arithmetic average but only get the #NUM! error for this volume of data.

    I can find nothing on this topic on MS or any of the Excel boards.

    Thanks

  2. #2
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try looking in the Excel help file:

    GEOMEAN
    See Also

    Returns the geometric mean of an array or range of positive data. For example, you can use GEOMEAN to calculate average growth rate given compound interest with variable rates.

    Syntax

    GEOMEAN(number1,number2, ...)

    Number1,number2,... are 1 to 30 arguments for which you want to calculate the mean.

    you can only use 30 arguments. 376 is way to many.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-12 12:51, Anonymous wrote:
    Has anyone used this function with a large volume of data? I am trying to get the geometric average of 376 data points to compare with the arithmetic average but only get the #NUM! error for this volume of data.

    I can find nothing on this topic on MS or any of the Excel boards.

    Thanks
    If appears to "max out" with an array of 170 values.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If appears to "max out" with an array of 170 values.
    Ahhh, I know what's happening... the product of the values is exceeding Excel's max value, 9.99999999999999E+307. See the Excel Help topic for "What does the error #NUM! mean?"

    [ This Message was edited by: Mark W. on 2002-03-12 13:38 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ahhh, I know what's happening... the product of the values is exceeding Excel's max value, 9.99999999999999E+307. See the Excel Help topic for "What does the error #NUM! mean?"
    GEOMEAN apparently takes the product of a range of numbers, then takes the nth-root
    of that product (where n is the number of arguments).

    You can work around this by figuring the nth-roots first, then taking the product.

    There's surely a clever UDF in someone's brain, just waiting to break out. But here's
    a straightforward way to do this.

    If B2:B377 have your numbers, have
    C1 = 1/COUNT(B2:B377)
    C2 = B2^$C$1 (and so on down to C377)
    Then D1 = PRODUCT(C2:C377) is your geometric mean.
    "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-12 14:06, Tim Francis-Wright wrote:
    Ahhh, I know what's happening... the product of the values is exceeding Excel's max value, 9.99999999999999E+307. See the Excel Help topic for "What does the error #NUM! mean?"
    GEOMEAN apparently takes the product of a range of numbers, then takes the nth-root
    of that product (where n is the number of arguments).

    You can work around this by figuring the nth-roots first, then taking the product.

    There's surely a clever UDF in someone's brain, just waiting to break out. But here's
    a straightforward way to do this.

    If B2:B377 have your numbers, have
    C1 = 1/COUNT(B2:B377)
    C2 = B2^$C$1 (and so on down to C377)
    Then D1 = PRODUCT(C2:C377) is your geometric mean.
    The following array formula works quite nicely....

    {=PRODUCT(B2:B377^(1/COUNT(B2:B377)))}

    Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Another array formula for the geometric mean is:

    {=10^AVERAGE(LOG10(B2:B377))}

    [That's log*10*, not log!]

    If you have a lot of data, this could
    be faster. I haven't done any trials,
    though.

    _________________
    "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

    [ This Message was edited by: Tim Francis-Wright on 2002-03-13 06:57 ]

  8. #8
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-12 15:43, Mark W. wrote:
    On 2002-03-12 14:06, Tim Francis-Wright wrote:
    Ahhh, I know what's happening... the product of the values is exceeding Excel's max value, 9.99999999999999E+307. See the Excel Help topic for "What does the error #NUM! mean?"
    GEOMEAN apparently takes the product of a range of numbers, then takes the nth-root
    of that product (where n is the number of arguments).

    You can work around this by figuring the nth-roots first, then taking the product.

    There's surely a clever UDF in someone's brain, just waiting to break out. But here's
    a straightforward way to do this.

    If B2:B377 have your numbers, have
    C1 = 1/COUNT(B2:B377)
    C2 = B2^$C$1 (and so on down to C377)
    Then D1 = PRODUCT(C2:C377) is your geometric mean.
    The following array formula works quite nicely....

    {=PRODUCT(B2:B377^(1/COUNT(B2:B377)))}

    Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.
    Hi All,

    I went searching through the archives and stumbled upon this excellent thread.

    A couple of notes...

    1. These work nicely.

    2. Tim's final post on this is a nice workaround. Very clever, and can be matched by

    {=EXP(AVERAGE(LN(Range)))}

    if one prefers the natural log rather than the base 10 log.

    3. Laurent Longre's morefunc.xll has a GEOMEAN.EXT function which overcomes the limits. Rather than (x1*x2*x3...*xn)^(1/n), it operates as (x1)^1/n * (x2)^1/n ...* (xn)^1/n

    4. The following UDF mirrors (or tries to) the functionality of Laurent's GEOMEAN.EXT. It also adds a possible calc which may be a mathematical impossibility (negative values in the range). If the number of terms is odd and the number of negative values is odd, this won't bomb. I'm not sure if this is mathematically correct, so be warned.

    ----------------------
    Public Function GEOMEAN2(ListRange)
    Dim RangeCount As Long, NegativeTally As Long
    Dim Intermediate As Double, Item

    RangeCount = WorksheetFunction.Count(ListRange)
    If RangeCount = 0 Then Intermediate = 0 Else Intermediate = 1
    NegativeTally = 0
    For Each Item In ListRange
    If Not IsNumeric(Item) Then RangeCount = RangeCount - 1
    Next Item

    For Each Item In ListRange
    If Item < 0 Then NegativeTally = NegativeTally + 1
    If Len(Item) = 0 Then
    'do nothing
    Else: Intermediate = Intermediate * ((Abs(Item)) ^ (1 / RangeCount))
    End If
    Next Item

    If (RangeCount Mod 2 = 0 And NegativeTally Mod 2 = 1) Then
    GEOMEAN2 = CVErr(xlErrNum)
    Else
    GEOMEAN2 = Intermediate * ((-1) ^ NegativeTally)
    End If
    End Function
    ----------------------

    Note that you can also work with logarithms and add terms rather than multiply them.

    5. Would like to request from the group suggested improvements to this.

    I don't like the two loops through the data that are required to handle non numeric entries in the the list.

    Any and all suggestions greatly appreciated.

    Thanks,
    Jay

  9. #9
    New Member
    Join Date
    Oct 2006
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: GEOMEAN

      
    Hi All,
    Sorry to reopen such an old thread but my question relates to it.

    I quite like the different options mentioned for calculating geometric mean, such as {=10^AVERAGE(LOG10(B2:B377))}

    Is there a way to code this in VBA using an array rather than a range?

    Many thanks

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com