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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike C - 12 May 2008 03:38 GMT

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.

Reply to this Message

JE McGimpsey - 12 May 2008 03:47 GMT

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.

In article
<48fff24b-518c-40b9-b5b9-c0283477b15b@56g2000hsm.googlegroups.com>,

> Hello - Anyone familiar with a way to take an average of a range of
> cells, but only for the cells that contain values.
[quoted text clipped - 9 lines]
>
> Thanks for any suggestions.

Reply to this Message

Rate this thread: