average function
 

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

average function
 

Hi!

Try this:

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

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

Biff

"Stephen" wrote in message
...
How do I average a column of numbers and exclude cells with zero values?

average function
 

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(rng0,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 function
 

=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" wrote in message
...
How do I average a column of numbers and exclude cells with zero values?

average function
 

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(rng0,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 function
 

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.

average function
 

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

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

average function
 

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:H1320,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"))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=536675