How to exclude "0" from average calculation

  • Thread starter Thread starter SteveMac
  • Start date Start date
=AVERAGE(IF(A1:A20<>0,A1:A20))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

(and adjust your range to suit, but you can't use the whole column--array
formulas don't like that.)
 
Can use it without the <>0 bit as well

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

still array entered though, and will ignore blanks and 0s
 
Ken Wright wrote
=AVERAGE(IF(A1:A20,A1:A20))

still array entered though, and will ignore blanks and 0s
Click to expand...

Always interested in new ways to adapt my existing formulae, I tried this
and got #DIV/0! if array entered. Not array entered worked.
 
That did the trick! Thanks!!!
-----Original Message-----
I like to keep it just for self documenting purposes.

11/03/2004

--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
David wrote
Ken Wright wrote


Always interested in new ways to adapt my existing formulae, I tried
this and got #DIV/0! if array entered. Not array entered worked.
Click to expand...

Clarifying, these results were if A1:A20 were empty.
 
You can't average a set of empty cells as the count is 0, hence the #DIV/0

You get the same result using the <>0 in there as well

When you say worked, I assume you mean because you didn't get #DIV/0, hence it
actually didn't work, as that is what it should have returned. Try a straight
=AVERAGE(Range) on an empty set and you will get #DIV/0
 
Ken Wright wrote
You can't average a set of empty cells as the count is 0, hence the
#DIV/0

You get the same result using the <>0 in there as well

When you say worked, I assume you mean because you didn't get #DIV/0,
hence it actually didn't work, as that is what it should have
returned. Try a straight =AVERAGE(Range) on an empty set and you will
get #DIV/0
Click to expand...

Understood. Just hoping that 'ignore blanks and 0's' meant what it said.

Actually I got a result of 0 when not array entered, which surprised me.

Oh, well. What I currently use in the sheet I tried it on:
=IF(COUNT(G5:K5),AVERAGE(G5:K5),"")
 
David wrote
Actually I got a result of 0 when not array entered, which surprised me.
Click to expand...

And if I entered some numbers in A1:A20, average worked properly. Still
questioning the need to array enter, I guess.
 
But if you don't array enter and only use Average, then the 0s get included in
the equivalent of your denominator, and the OPs question was how to exclude them
from this, eg:-

A
1 20
2 15
3 10
4 0
5 10

1) =AVERAGE(A1:A5) = 11
2) =AVERAGE(IF(A1:A5,A1:A5)) entered normally will give 11
3) =AVERAGE(IF(A1:A5,A1:A5)) array entered will give 13.75

Both 1 and 2 include the 0 in A4 in the average, and the OP specifically wants
to exclude this value, hence the average of the remaining values should be
13.75, as given *only* by the array entered formula.
 
Ken Wright wrote
But if you don't array enter and only use Average, then the 0s get
included in the equivalent of your denominator, and the OPs question
was how to exclude them from this, eg:-

A
1 20
2 15
3 10
4 0
5 10

1) =AVERAGE(A1:A5) = 11
2) =AVERAGE(IF(A1:A5,A1:A5)) entered normally will give 11
3) =AVERAGE(IF(A1:A5,A1:A5)) array entered will give 13.75

Both 1 and 2 include the 0 in A4 in the average, and the OP
specifically wants to exclude this value, hence the average of the
remaining values should be 13.75, as given *only* by the array entered
formula.
Click to expand...

If A4 is blank, result is 13.75 no matter which way. 'and will ignore
blanks *and* 0s' if array entered must be taken literally (i.e. not 'blanks
*or* 0s) which led me on this chase in the first place.

Further observation that affected my particular sheet:
If formula is *not* array entered and is placed in a row outside the range
or in the same column as the range, result is #VALUE! Hmmm...

Anyway, point taken.
 
You could check to see if there are any numbers <> 0 to average first.

=IF(COUNTIF(A1:A20,"<>0")=0,"No nice numbers to average",
AVERAGE(IF(A1:A20<>0,A1:A20)))

(one cell, still array entered)

(and I kept the <>0 since I like it <bg>)
 
Dave Peterson wrote
You could check to see if there are any numbers <> 0 to average first.

=IF(COUNTIF(A1:A20,"<>0")=0,"No nice numbers to average",
AVERAGE(IF(A1:A20<>0,A1:A20)))
Click to expand...

True. Actually I'm using this in one of my sheets:
=IF(COUNT(G5:K5),AVERAGE(G5:K5),"")
 
Back
Top