Problem with GEOMEAN - returns #NUM error

[Guest]

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?

 

[Fredrik Wahlgren]

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?

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

 

[N Harkawat]

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

 

[Guest]

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]

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

 

[Myrna Larson]

Harlan Grove has posted this array formula in the past:

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

entered with CTRL+SHIFT+ENTER.

 

[Harlan Grove]

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