GEOMEAN Function

[KD]

First of all, I'm no software expert, however, I have a question. I am
trying to run the geomean for a list of numbers. The numbers are in a range
that includes more than 200 numbers. However, I get an error message
whenever I use the GEOMEAN function for more than 117 cells. Is there a way
to get past this limit??

 

[ufo_pilot]

GEOMEAN
is for positive numbers, be sure there are no negatives in your range.this
will throw you an error, but you should be able to get past 117 cells in a
column easily.

 

[Martin Brown]

First of all, I'm no software expert, however, I have a question. I am
trying to run the geomean for a list of numbers. The numbers are in a range
that includes more than 200 numbers. However, I get an error message
whenever I use the GEOMEAN function for more than 117 cells. Is there a way
to get past this limit??

Excel appears to flake out whenever the product of the numbers in
GEOMEAN overflows a basic IEEE floating point representation. 1.7E+308

Odd really since in other algorithms they hold intermediate results in
temporary reals which would give the naieve algorithm a lot more
headroom 1.1E+4932

Provided your numbers are all positive and non zero (and if they are not
it is pretty weird to use GEOMEAN) you can compute the same result
fairly easily. Take logs compute average, then exponentiate.

{=10^AVERAGE(LOG(A$1:A200))

Should do the trick entered as an array formula.

Regards,
Martin Brown

 

[joeu2004]

I am trying to run the geomean for a list of numbers. The
numbers are in a range that includes more than 200 numbers.
However, I get an error message whenever I use the GEOMEAN
function for more than 117 cells. Is there a way to get past
this limit??

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)?

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

By the way, if the parameters of GEOMEAN() represent a series
of ratios y[1]=a[1]/a[0], y[2]=a[2]/a[1],..., y[n]=a[n]/a[n-1],
the geometric mean can be computed simply by (a[n]/a[0])^(1/n).
This might reduce computational error due to computer arithmetic
that involves a series of n multiplications (y[1]*y[2]*...*y[n]).

 

[Harlan Grove]

Martin Brown wrote...
....

Excel appears to flake out whenever the product of the numbers in
GEOMEAN overflows a basic IEEE floating point representation. 1.7E+308

Odd really since in other algorithms they hold intermediate results in
temporary reals which would give the naieve algorithm a lot more
headroom 1.1E+4932

....

Odder still since the geometric mean is just e raised to power of the
average of the logarithms or the product of the Nth roots of N values.
80-bit reals aren't necessary for better results. Better algorithms
would have provided ample robustness.

 

[Martin Brown]

Martin Brown wrote...
...


...

Odder still since the geometric mean is just e raised to power of the
average of the logarithms or the product of the Nth roots of N values.
80-bit reals aren't necessary for better results. Better algorithms
would have provided ample robustness.

Yes. What I was commenting on was that they have implemented a very bad
algorithm in an absolutely disastrous fashion. Two mistakes compounded!

Another one to add to the Mickeysoft hall of statistical infamy.

Regards,
Martin Brown

 

[KD]

I'm using Excel 2002. Probably need to upgrade. . .The numbers are a range
of simple numbers, all positive, no zeros or blank cells, The ranges I'm
using the geomean function are between a range of three numbers and 234
numbers. . .works every time except when the number of cells go over 117. The
error message I get is the #NUM! message. I will try using the log and
exponent approach suggested by another input. Thanks,

I am trying to run the geomean for a list of numbers. The
numbers are in a range that includes more than 200 numbers.
However, I get an error message whenever I use the GEOMEAN
function for more than 117 cells. Is there a way to get past
this limit??

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)?

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

By the way, if the parameters of GEOMEAN() represent a series
of ratios y[1]=a[1]/a[0], y[2]=a[2]/a[1],..., y[n]=a[n]/a[n-1],
the geometric mean can be computed simply by (a[n]/a[0])^(1/n).
This might reduce computational error due to computer arithmetic
that involves a series of n multiplications (y[1]*y[2]*...*y[n]).

 

[Harlan Grove]

(e-mail address removed) 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.

 

[joeu2004]

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. [...] GEOMEAN not only chokes
on overflow (in which case it returns #NUM!), it also chokes
on underflow (it returns 0).

Sure! But since the OP failed to explain the error he got, I
wanted to address his apparent belief that there was limit
of 117 per se. The OP wrote: "whenever I use the GEOMEAN
function for more than 117 cells. Is there a way to get past
this limit?".

I also wanted to emphasize the general need for people who
encounter problems to provide sufficient details in order to
reasonably identify the "root cause" of the problem. (I
sometimes forget myself.)

That said, I personally never use GEOMEAN(). I prefer to
compute the average of the log values. That lends itself easily
to also computing the std dev and std err, which are useful in
some applications of the geometric mean.