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