View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default 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))))