average function

[Stephen]

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

"Stephen" <[email protected]> wrote in message
news:[email protected]...
> How do I average a column of numbers and exclude cells with zero values?

[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.

[Stephen]

=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


"Biff" wrote:

> Hi!
>
> Try this:
>
> Entered as an array using the key combination of CTRL,SHIFT,ENTER:
>
> =AVERAGE(IF(A1:A20<>0,A1:A20))
>
> Biff
>
> "Stephen" <[email protected]> wrote in message
> news:[email protected]...
> > How do I average a column of numbers and exclude cells with zero values?
>
>
>

[Stephen]

What does "rng" stand for in this function?

Stephen


"Harlan Grove" wrote:

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

[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:
....
>>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.

[Harlan Grove]

Stephen wrote...
>What does "rng" stand for in this function?
....

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