average of a range, but only for the cells that contain values

  • Thread starter Thread starter Mike C
  • Start date Start date
M

Mike C

Hello - Anyone familiar with a way to take an average of a range of
cells, but only for the cells that contain values.

So for example:

9
0
9
9

The average of the above four cells should produce an output of 9.

Thanks for any suggestions.
 
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

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

Or, if using XL07/08:

=AVERAGEIF(A1:A4,"<>0")

Note: Of course, 0 is a value. If its possible that 0 is a *valid*
value, then you'll need to figure out a different solution.
 
Back
Top