GEOMEAN

G

Guest

Guest
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top