Problem with GEOMEAN - returns #NUM error

We have workbook that uses the GEOMEAN function on a column of data. The
values being analyzed are returned by the formula =IF(M1=0," ",M1) to ensure
that there are no zero values. On one sheet =GEOMEAN(N2:n784) works find; On
another sheet =GEOMEAN(N2:n1450) returns the #NUM error. The only difference
between the two sheets is the number of rows. On the sheet that returns the
error, if we change the formula to =GEOMEAN(N2:N394) it works, but as soon as
we include cell N395 or higher, it returns the error.

Any Suggestions?

--
Dan Knight

"Dan Knight" <DanKnight@discussions.microsoft.com> wrote in message
news:D2DC6BEB-1631-4084-93DE-B8327594A303@microsoft.com...
> We have workbook that uses the GEOMEAN function on a column of data. The
> values being analyzed are returned by the formula =IF(M1=0," ",M1) to
ensure
> that there are no zero values. On one sheet =GEOMEAN(N2:n784) works find;
On
> another sheet =GEOMEAN(N2:n1450) returns the #NUM error. The only
difference
> between the two sheets is the number of rows. On the sheet that returns
the
> error, if we change the formula to =GEOMEAN(N2:N394) it works, but as soon
as
> we include cell N395 or higher, it returns the error.
>
> Any Suggestions?
>
> --
> Dan Knight
>
>

My guess is that there is an internal overflow somewhere.

> The only difference between the two sheets is the number of rows
Are the numbers in the range the same?

/Fredrik

Fredrik;
I appreciate your willingness to answer my problem, however, I'm afraid I
have no idea what your response actually means:
1) What is "an internal overflow"
2) How do I tell if that is the case?
3) Practical suggestions on how to overcome or work around said "internal
overflow".

Thanks,


"Fredrik Wahlgren" wrote:

>
> "Dan Knight" <DanKnight@discussions.microsoft.com> wrote in message
> news:D2DC6BEB-1631-4084-93DE-B8327594A303@microsoft.com...
> > We have workbook that uses the GEOMEAN function on a column of data. The
> > values being analyzed are returned by the formula =IF(M1=0," ",M1) to
> ensure
> > that there are no zero values. On one sheet =GEOMEAN(N2:n784) works find;
> On
> > another sheet =GEOMEAN(N2:n1450) returns the #NUM error. The only
> difference
> > between the two sheets is the number of rows. On the sheet that returns
> the
> > error, if we change the formula to =GEOMEAN(N2:N394) it works, but as soon
> as
> > we include cell N395 or higher, it returns the error.
> >
> > Any Suggestions?
> >
> > --
> > Dan Knight
> >
> >
>
> My guess is that there is an internal overflow somewhere.
>
> > The only difference between the two sheets is the number of rows
> Are the numbers in the range the same?
>
> /Fredrik
>
>
>
>

"Dan Knight" <DanKnight@discussions.microsoft.com> wrote in message
news:D35DB05B-3DFF-4E61-8240-65C1C6A23A6D@microsoft.com...
> Fredrik;
> I appreciate your willingness to answer my problem, however, I'm afraid I
> have no idea what your response actually means:
> 1) What is "an internal overflow"
> 2) How do I tell if that is the case?
> 3) Practical suggestions on how to overcome or work around said "internal
> overflow".
>
> Thanks,
>
>

GEOMEAN returns the nth root of the product. I think the product of these
values is simply too big. Excel can't handle numbers bigger than some value
which I think you have exceeded. Or you may have a zero somewhere.

You can find an add-in that implements geomean with ahigher capacity here:
http://longre.free.fr/english/

Why do you want to calculate geomean with so many values?

/Fredrik

Harlan Grove has posted this array formula in the past:

=EXP(AVERAGE(LN(A2:A200)))

entered with CTRL+SHIFT+ENTER.

On Fri, 18 Feb 2005 00:20:20 +0100, "Fredrik Wahlgren"
<fredrik.p.wahlgren@mailbox.swipnet.se> wrote:

>
>"Dan Knight" <DanKnight@discussions.microsoft.com> wrote in message
>news:D35DB05B-3DFF-4E61-8240-65C1C6A23A6D@microsoft.com...
>> Fredrik;
>> I appreciate your willingness to answer my problem, however, I'm afraid I
>> have no idea what your response actually means:
>> 1) What is "an internal overflow"
>> 2) How do I tell if that is the case?
>> 3) Practical suggestions on how to overcome or work around said "internal
>> overflow".
>>
>> Thanks,
>>
>>
>
>GEOMEAN returns the nth root of the product. I think the product of these
>values is simply too big. Excel can't handle numbers bigger than some value
>which I think you have exceeded. Or you may have a zero somewhere.
>
>You can find an add-in that implements geomean with ahigher capacity here:
>http://longre.free.fr/english/
>
>Why do you want to calculate geomean with so many values?
>
>/Fredrik
>
>

Dan
In lieu of Geomean you may use the following formula and may probably not
give you an error

=PRODUCT(1+n2:n1450)^(1/(COUNTA(n2:n1450)))-1
aray entered (Ctrl + Shift + Enter)

I simply entered 0.02 from Cells N2 thru N1450 and the formula correctly
returned 0.02 whereas geomean gave 0



"Dan Knight" <DanKnight@discussions.microsoft.com> wrote in message
news:D2DC6BEB-1631-4084-93DE-B8327594A303@microsoft.com...
> We have workbook that uses the GEOMEAN function on a column of data. The
> values being analyzed are returned by the formula =IF(M1=0," ",M1) to
> ensure
> that there are no zero values. On one sheet =GEOMEAN(N2:n784) works find;
> On
> another sheet =GEOMEAN(N2:n1450) returns the #NUM error. The only
> difference
> between the two sheets is the number of rows. On the sheet that returns
> the
> error, if we change the formula to =GEOMEAN(N2:N394) it works, but as soon
> as
> we include cell N395 or higher, it returns the error.
>
> Any Suggestions?
>
> --
> Dan Knight
>

N Harkawat wrote...
>In lieu of Geomean you may use the following formula and may probably
not
>give you an error
>
>=PRODUCT(1+n2:n1450)^(1/(COUNTA(n2:n1450)))-1
>aray entered (Ctrl + Shift + Enter)
>
>I simply entered 0.02 from Cells N2 thru N1450 and the formula
correctly
>returned 0.02 whereas geomean gave 0

This isn't equivalent to GEOMEAN(N2:N1450). If the OP were averaging
percentage changes *AND* had shown the original formula as
=GEOMEAN(1+N2:N1450), then your formula would *STILL* overflow in the
PRODUCT call if GEOMEAN were overflowing.

The OP's choices are the array formulas

=PRODUCT(N2:N1450^(1/1449))

or

=EXP(AVERAGE(LN(N2:N1450)))

or the nonarray formula

=EXP(SUMPRODUCT(LN(N2:N1450))/1449)

All these assume the range is filled with numbers. If there were any
blank or text values, prophylactic IF calls would be needed, in which
case the practical solution would be the array formula

=EXP(AVERAGE(IF(ISNUMBER(N2:N1450),LN(N2:N1450))))