average function

[Guest]

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

 

[Biff]

Hi!

Try this:

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

=AVERAGE(IF(A1:A20<>0,A1:A20))

Biff

 

[Harlan Grove]

Biff wrote...

Try this:

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

=AVERAGE(IF(A1:A20<>0,A1:A20))

....

As I never tire of pointing out, better to use

=AVERAGE(IF(rng>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.

 

[Guest]

=AVERAGE(IF(H2:H132<>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?

Stephen

 

[Guest]

What does "rng" stand for in this function?

Stephen

 

[Harlan Grove]

Stephen wrote...

=AVERAGE(IF(H2:H132<>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? ....
"Biff" wrote: ....

....

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.

 

[Harlan Grove]

Stephen wrote...

What does "rng" stand for in this function?

....

It's a placeholder for whatever range address you want to use.

 

[daddylonglegs]

If I may be permitted......

The "rng" in Harlan's formula is simply "range", 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:H132>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,">0"))