Oops.. please ignore the previous post...

=SUM(B:B,E:E,H:H)/(COUNTIF(B:B,"0")+COUNTIF(E:E,"0")+COUNTIF(H:H," 0"))

I am sure there must be an easy way...

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Try

=AVERAGEIF(B:B:E:E:H:H,"0")

OR

=SUM(B:B:E:E:H:H)/COUNTIF(B:B:E:E:H:H,"0")


If this post helps click Yes
---------------
Jacob Skaria


"markmcd" wrote:

I'm having trouble with working out a formula to give the average of non zero
data where the data is not in contigous cells.

I have data for Feb, Mar, Apr per truck so Feb will appear in column B, E,
H. If B, E, H are zero (any one of them) then I don't want to include as part
of the average.
I have used the averageif as an array to not count zero values and this
gives an error. I have also used the sum/countif but countif doesn't seem to
like non contigous cells. I used AND to get around the non contiguous
situation but that didn't work either

I'm surprised by the Excel functionality when it comes to average, the most
common scenarios are those where formulas have to be more complex. Basically
all I want is to have 10,0,5,15,0,0 averaged excluding zeros so in this case
the average would be 10 and not 6. Markedly different.