Sums with exclusions

[keri]

ok, you have a column (1-30) with numbers including zero. now 20 of
those #'s are above 0 and 10 are below. say the sum of all the numbers
is 46. now the average (46/30) is 1.5. but what i really want to know
is the average of the numbers, not including the zeros. so that would
be 46/20 = 2.3.
so is there a formula that will do this for me?

 

[shades]

Assume column A has the numbers:

=SUM($A$1:$A$30)/COUNT($A$1:$A$30,"<>0")

 

[Dana DeLouis]

The following array formula should work:

=AVERAGE(IF(rng<>0,rng))

Ctrl+Shift+Enter.

HTH. :>)