GEOMEAN

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

[Steve Hartman]

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.

[Mark W.]

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.

[Mark W.]

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 ]

[Tim Francis-Wright]

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.

[Mark W.]

On 2002-03-12 14:06, Tim Francis-Wright wrote:

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.

[Tim Francis-Wright]

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 ]

[Jay Petrulis]

On 2002-03-12 15:43, Mark W. wrote:

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

[emanresu]

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