Why do I get #NUM! for a GEOMEAN calc on a set of positive vals

[Nick Curties]

I have a set of around 400 values in a sheet which range between 1 and
200,000. When I try to calculate the GEOMEAN value I get a #NUM! result. If I
calculate the accumulating GEOMEAN (A1:A1, A1:A2, A1:A3 etc.) I find that the
#NUM! starts when I hit the 174th item, which looks normal at about 139. If I
calculate the GEOMEAN in chunks of 50 values (A1:A50, A2:A51, A3:A52 ...
A150:A199 etc.) the calculation goes past this 174th item with no problems.
If I manually edit the value, I can set it to any number from 0 to 51 and
the calculation will take place correctly; however, the #NUM! returns at the
calculation of the 175th item.

 

[Mike H]

Yoe get a NUM error if the dataset includes negative values, zeroes or (and
most likely) with a very large dataset you are hitting a number larger than
Excel can handle so it bombs with a NUM error.

use instead

=EXP(AVERAGE(LN(A1:A300)))

Entered as an array ctrl+shift+enter. It will wobble for neg's and zeroes
but overcomes the Excel max va,ue problem.

Mike

 

[Nick Curties]

Thanks for the reply. When I use the formula you provided, I get a #VALUE!
result. All the values I'm using are positive and I have tried copying the
values to a different area to ensure there is nothing strange in any of the
cells and rounding to the nearest integer. For information, the Geometric
mean is just below 60 when the error occurs.

 

[Ron Rosenfeld]

On Sat, 23 Jun 2007 05:27:00 -0700, Nick Curties <Nick

I have a set of around 400 values in a sheet which range between 1 and
200,000. When I try to calculate the GEOMEAN value I get a #NUM! result. If I
calculate the accumulating GEOMEAN (A1:A1, A1:A2, A1:A3 etc.) I find that the
#NUM! starts when I hit the 174th item, which looks normal at about 139. If I
calculate the GEOMEAN in chunks of 50 values (A1:A50, A2:A51, A3:A52 ...
A150:A199 etc.) the calculation goes past this 174th item with no problems.
If I manually edit the value, I can set it to any number from 0 to 51 and
the calculation will take place correctly; however, the #NUM! returns at the
calculation of the 175th item.

With 174 entries, my guess is that your formula is producing a value outside of
the range allowed by Excel. I don't know what algorithm Excel uses to
calculate GEOMEAN, but if it something like

=POWER(PRODUCT(A1:An),1/COUNT(A1:An))

It is possible that the PRODUCT(A1:An) is generating a number out of range.
You could check this hypothesis on your data, especially seeing what happens
with values of 51 and 52 in A174
--ron

 

[Mike H]

Did you enter it as an array?

Type the formula then with the cursor still in the formula bar hit

Ctrl+Shift+Enter

Mike

 

[Nick Curties]

No, but when I did I got the required result; thanks very much. I've never
used arrays before, so this gives me something new to play with.

Thanks again.
Nick

 

[Mike H]

Yor welcome and thanks for the feedback

No, but when I did I got the required result; thanks very much. I've never
used arrays before, so this gives me something new to play with.

Thanks again.
Nick

 

[Harlan Grove]

Yoe get a NUM error if the dataset includes negative values, zeroes or (and
most likely) with a very large dataset you are hitting a number larger than
Excel can handle so it bombs with a NUM error.

use instead

=EXP(AVERAGE(LN(A1:A300)))

....

FWIW, you could use

=EXP(SUMPRODUCT(LN(A1:A300)/COUNT(A1:A300)))

which doesn't require array entry and should provide more precision (only
one decimal place given the OP's range of values).

 

[Harlan Grove]

. . . I don't know what algorithm Excel uses to
calculate GEOMEAN, but if it something like

=POWER(PRODUCT(A1:An),1/COUNT(A1:An))

....

Bingo!

And, FWIW, Gnumeric used the same algorithm until a few people (me included)
pointed out its shortcomings. The Gnumeric team fixed it. And OpenOffice
Calc has always used a better algorithm. Will Microsoft ever fix Excel's
GEOMEAN?

 

[Ron Rosenfeld]

Will Microsoft ever fix Excel's GEOMEAN?

I guess I won't hold my breath waiting!

But wait -- they did change the LINEST algorithm after, what, 15+ years?
--ron

 

[Harlan Grove]

I guess I won't hold my breath waiting!

But wait -- they did change the LINEST algorithm after, what, 15+ years?

Yeah, but that was after a substantial amount of journal articles had
pointed out just how bad Excel's regression functions were. I doubt
researchers are going to waste their time picking on GEOMEAN. That's my job
(along with MOD).

 

[Ron Rosenfeld]

Yeah, but that was after a substantial amount of journal articles had
pointed out just how bad Excel's regression functions were. I doubt
researchers are going to waste their time picking on GEOMEAN. That's my job
(along with MOD).

Yes, I was being facetious. And I applaud your efforts.

One of the inexplicable facts, to me, was that they already had access to a
pretty good algorithm in the graphing module.
--ron

 

[Harlan Grove]

One of the inexplicable facts, to me, was that they already had access to a
pretty good algorithm in the graphing module.

Which implies that the graphics team reinvented the wheel, but happily they
decided to make it round rather than rectangular.

 

[Jerry W. Lewis]

Which [chart trendline] they "improved" in 2007 to the point that it is no
longer trustworthy
http://groups.google.com/group/microsoft.public.excel.charting/msg/67353c068ee07b94
http://groups.google.com/group/microsoft.public.excel.charting/browse_frm/thread/4328cd532baaa078
http://groups.google.com/group/microsoft.public.excel.charting/browse_frm/thread/e3752db0fedc2cbf

Jerry

:
....

 

[Ron Rosenfeld]

Which [chart trendline] they "improved" in 2007 to the point that it is no
longer trustworthy
http://groups.google.com/group/microsoft.public.excel.charting/msg/67353c068ee07b94
http://groups.google.com/group/microsoft.public.excel.charting/browse_frm/thread/4328cd532baaa078
http://groups.google.com/group/microsoft.public.excel.charting/browse_frm/thread/e3752db0fedc2cbf

Jerry

:
...

One of the inexplicable facts, to me, was that they already had access to a
pretty good algorithm in the graphing module.

I have Office 2007 (free upgrade for me) sitting on my desk and have been
hesitant to install it. You've just brought up another reason!
--ron