Geomean function
"Stephen" <none wrote...
....
. . . When I type GEOMEAN into Help, it tells me that the
function can take from 1 to 30 arguments (what you called "data
points"). So I suppose any more than that and the result isn't
defined. . . .
....
Arguments aren't the same as data points.
=GEOMEAN(1,2,3,4)
has 4 arguments AND 4 data points, but
=GEOMEAN(B5:B1004)
has ONE argument but could span 1000 data points.
The OP's problem is driven by having 300-odd values of around 10, and
that means the result of multiplying all the values together would be
around 10^300, which is the high end of Excel's numeric capabilities.
In other words, GEOMEAN is suffering from numeric overflow, which is
due to Excel's rather stupid implementation, equivalent to
GEOMEAN(x) == PRODUCT(x) ^ (1 / COUNT(x))
The expedient solution to this is using something other than GEOMEAN.
If all values in the range in question were positive numbers, then the
easiest is
=EXP(AVERAGE(LN(data_points)))
If there were missing values, then an array formula is necessary
=EXP(AVERAGE(IF(ISNUMBER(data_points),LN(data_poin ts))))
|