ExcelBanter - View Single Post

wrote...
....
What exactly are you doing? That is, exactly what does your
GEOMEAN() function usage look like? What version of Excel
are you using? Exactly what error message do you get? And
what is the range of your values (min, max)?

Excel version doesn't matter. Really old versions (either 3 or 4 &
prior) don't have it, and the newer versions that do share the same
execrable implementation.

I have no problem with GEOMEAN(A1:A200) using Excel 2003,
where A1:A200 ranges from 0.00543 to 1.964284.
....

And with a mix straddling unity you shouldn't have much of a problem
with it. But it's EASY to break. Fill A1:A20 with the formula
=RAND()^25. Enter =GEOMEAN(A1:A20) in B2 and =EXP(AVERAGE(LN(A1:A20)))
[array formula] in B2. Press [F9] until B1 returns 0 while B2 return a
positive value. GEOMEAN not only chokes on overflow (in which case it
returns #NUM!), it also chokes on underflow (it returns 0).

While overflow/underflow should be rare, the frequency of GEOMEAN
questions over the years would imply it's not as rare as Microsoft's
Excel programmers may believe. It deserves a better implementation.