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" wrote in message
...
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
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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 he
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"
wrote:


"Dan Knight" wrote in message
...
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 he
http://longre.free.fr/english/

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

/Fredrik

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