geomean

[Guest]

I am trying to do a simple geomean calculation and the #NUM keeps coming up.
All my numbers are positive, above zero, and I have even eliminated the empty
cells. I cannot figure out what I am doing wrong. I am clicking the formula
from the dropdown menu, not creating it myself. I did copy the numbers from
another spreadsheet, but I formatted them to be numbers, two digits. Any
ideas?

 

[Myrna Larson]

Formatting doesn't change the underlying data type. Have you checked that
Excel "thinks" they are numbers? Do that by widening the column. If the
horizontal alignment is "general", numbers will align right, text will align
left. If they are in fact text, Edit/Copy a blank cell, then select the
problem column and Edit/Paste Special and select the Values and Add options.
This will convert to numbers.

 

[Guest]

Thanks Myrna - all the numbers (750 rows/21 columns) align right, so I think
they are all numbers.

 

[Myrna Larson]

I think you just have too much data.

I expect that, "under the hood", Excel is getting an overflow error with this
many (15750) cells. I just filled the range A1:U750 with the formula
=RANDBETWEEN(1,3), then converted the formulas to values.

The formula =GEOMEAN(A1:U750) gave me a #NUM! error.

In fact the formula =PRODUCT(A1:U750) also gave a #NUM! error. Experimenting
with some PRODUCT formulas, =PRODUCT(A1:A750) worked, but A1:B750 (1500 cells)
gave the same NUM error.

If I used a formula that put a 1 in half of the cells and 2 in the other half,
I got #NUM! with both PRODUCT and GEOMEAN.

 

[Guest]

I wondered if that was the case (that's why I mentioned the numbers), because
if I change the formula to include only a portion of the column, it does just
fine. There doesn't appear to be a logic or pattern to the cutoff in each
column, but I'm sure there must be. I guess I'll just figure it in sections.
Thank you for your help - I really appreciate it.

 

[Dana DeLouis]

Using Excel XP, I did not get an error using this Array formula. Perhaps
you could use this as a workaround.

=PRODUCT(EXP(LN(A1:U750)/15750))

One technique. Select all you cells in your table (perhaps with
Ctrl+Shift+"*" ), and in the lower right of the worksheet, right click the
selection status, and change the default of Sum, to "Count Nums." You
should see 15750.

HTH
Dana DeLouis

 

[Harlan Grove]

Formatting doesn't change the underlying data type. Have you checked that
Excel "thinks" they are numbers? Do that by widening the column. If the
horizontal alignment is "general", numbers will align right, text will align
left. If they are in fact text, Edit/Copy a blank cell, then select the
problem column and Edit/Paste Special and select the Values and Add options.
This will convert to numbers.

....

Unnecessary. Like all other aggregation functions, GEOMEAN ignores blank
cells and cells evaluating as text or boolean. It does propagate error
values, but I'd guess that isn't the OP's problem.

 

[Harlan Grove]

If I used a formula that put a 1 in half of the cells and 2 in the
other half, I got #NUM! with both PRODUCT and GEOMEAN.

....

Quality software.

 

[Harlan Grove]

Using Excel XP, I did not get an error using this Array formula. Perhaps
you could use this as a workaround.

=PRODUCT(EXP(LN(A1:U750)/15750))

....

Hardcoded numbers are BAD. Better to use

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

 

[Myrna Larson]

Before you do it in pieces, try Harlan's suggestion of the array formula

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

It worked for me with cells A1:U750 filled with via the formula =RAND()*10+1,
i.e. between ~1 and ~11.

OTOH, when I put =GEOMEAN(A1:U1) in V1 and copied down for 750 rows, it still
couldn't handle GEOMEAN(V1:V750). I had to do

GEOMEAN(GEOMEAN(V1:V375),GEOMEAN(V376:V750))

The latter gave the same result as Harlan's array formula.

I expect problem is that they are multiplying rather adding the logs, and the
product is overflowing the maximum for a IEEE double, approx 1.798 * 10^308

 

[Myrna Larson]

GEOMEAN ignores blank cells and cells evaluating as text or boolean

Thanks for that, Harlan. I should have checked it in Help...

 

[Jerry W. Lewis]

Or more generally

=EXP(AVERAGE(IF(ISNUMBER(A1:J900),LN(A1:J900))))

which preserves the property of GEOMEAN that non-numeric data is ignored.

Jerry

 

[Myrna Larson]

Yes. I thought of that this AM, but you beat me to it!

 

[Myrna Larson]

GEOMEAN ignores blank cells and cells evaluating as text or boolean

WRT to Harlan's comment (above) that checking the data type isn't necessary,
because GEOMEAN ignores text, booleans, blanks, etc, if there are *no* numbers
in the range, i.e. *everything* is text, GEOMEAN will give you the #NUM!
error. I that "all text" could be possible if the data were downloaded from a
web site.

 

[Harlan Grove]

WRT to Harlan's comment (above) that checking the data type isn't necessary,
because GEOMEAN ignores text, booleans, blanks, etc, if there are *no*
numbers in the range, i.e. *everything* is text, GEOMEAN will give you the
#NUM! error. I that "all text" could be possible if the data were downloaded
from a web site.

OK, but that only differs from AVERAGE's result in the same situation by
returning #NUM! (like 0^0) rather than #DIV/0! (0/0).

 

[Myrna Larson]

Yes, of course, but *in theory* it could be an explanation of the #NUM! error
that the OP was seeing. I think that ANY formula will fail with some kind of
error if there are NO numbers in the range, right?

 

[Myrna Larson]

I should have said that you'll get an error with no numbers in the range
*unless* you trap for no numbers in the formula, i.e. maybe something like

=IF(COUNT(A1:U750)=0,0,EXP(AVERAGE(LN(A1:U750))))

 

[Harlan Grove]

Yes, of course, but *in theory* it could be an explanation of the
#NUM! error that the OP was seeing. I think that ANY formula will
fail with some kind of error if there are NO numbers in the range,
right?

A1 blank, A2 evaluates to FALSE.

=MAX(A1:A2)
=MIN(A1:A2)
=COUNT(A1:A2)
=SUM(A1:A2)

If you mean formulas that calculate moments, you're right.

 

[Dana DeLouis]

Thanks Harlan! Never even saw that one. Took me a few minutes to see it.
;>(
Just for Geewiz. An interesting test on a large data set is to use just
Rand() for the data. As the size of the data gets larger, the limit of
Geomean approaches =EXP(-1) [0.367..]

Thanks again.
Dana DeLouis

 

[Myrna Larson]

I meant any formulas devised to work around the GEOMEAN problem. LN isn't as
"helpful" as MAX, MIN, COUNT, SUM, etc.