software

How do I average a column of numbers and exclude cells with zero values?

Hi!

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=AVERAGE(IF(A1:A20lt;gt;0,A1:A20))

Biff

quot;Stephenquot; gt; wrote in message
...
gt; How do I average a column of numbers and exclude cells with zero values?
Biff wrote...
gt;Try this:
gt;
gt;Entered as an array using the key combination of CTRL,SHIFT,ENTER:
gt;
gt;=AVERAGE(IF(A1:A20lt;gt;0,A1:A20))
....

As I never tire of pointing out, better to use

=AVERAGE(IF(rnggt;0,rng))

because if values could be negative as well as positive, then
legitimate values could also be zero, so the only time it make sense
(mathematically) to exclude zeros is when all values should be only
positive or only negative.=AVERAGE(IF(H2:H132lt;gt;0,H2:H132)) is the function I wrote from your example
and I am still getting an error. Is there anything wrong with the function I
have typed?

Stephenquot;Biffquot; wrote:

gt; Hi!
gt;
gt; Try this:
gt;
gt; Entered as an array using the key combination of CTRL,SHIFT,ENTER:
gt;
gt; =AVERAGE(IF(A1:A20lt;gt;0,A1:A20))
gt;
gt; Biff
gt;
gt; quot;Stephenquot; gt; wrote in message
gt; ...
gt; gt; How do I average a column of numbers and exclude cells with zero values?
gt;
gt;
gt;

What does quot;rngquot; stand for in this function?

Stephenquot;Harlan Grovequot; wrote:

gt; Biff wrote...
gt; gt;Try this:
gt; gt;
gt; gt;Entered as an array using the key combination of CTRL,SHIFT,ENTER:
gt; gt;
gt; gt;=AVERAGE(IF(A1:A20lt;gt;0,A1:A20))
gt; ....
gt;
gt; As I never tire of pointing out, better to use
gt;
gt; =AVERAGE(IF(rnggt;0,rng))
gt;
gt; because if values could be negative as well as positive, then
gt; legitimate values could also be zero, so the only time it make sense
gt; (mathematically) to exclude zeros is when all values should be only
gt; positive or only negative.
gt;
gt;

Stephen wrote...
gt;=AVERAGE(IF(H2:H132lt;gt;0,H2:H132)) is the function I wrote from your example
gt;and I am still getting an error. Is there anything wrong with the function I
gt;have typed?
....
gt;quot;Biffquot; wrote:
....
gt;gt;Entered as an array using the key combination of CTRL,SHIFT,ENTER:
....

It needs to be an array formula. You don't just type it an press
[Enter], you type it, hold down [Ctrl] and [Shift] keys and press
[Enter].

If the error is #VALUE!, then the odds are you didn't enter it as an
array formula.

If the error is #DIV/0!, then the odds are there are no nonzero numbers
in H2:H132.

If the error is anything else, then you have that error value in at
least one cell in H2:H132.Stephen wrote...
gt;What does quot;rngquot; stand for in this function?
....

It's a placeholder for whatever range address you want to use.
If I may be permitted......

The quot;rngquot; in Harlan's formula is simply quot;rangequot;, i.e. in your case
H2:H132

Following Harlan's argument I assume you have only positive values and
zeroes and you can therefore use

=AVERAGE(IF(H2:H132gt;0,H2:H132))

This formula needs to be confirmed with CTRL SHIFT ENTER (as Biff says)
so that curly braces appear around the formula in the formula bar,
alternatively you can use a formula which just requires ENTER

=SUM(H2:H132)/MAX(1,COUNTIF(H2:H132,quot;gt;0quot;))--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=536675