Hello all,

I am using a formula to find the average of values between cells D5 and AG5.

=(SUM($D$5:$AG$5)/30)

My problem is, I don't always use every cell, sometimes I only use 10 or 20 of them. This, as I'm sure you all know, completely messes things up. What can I add to this formula to have it not take into account zeros, and to adjust the dividing numbers accordingly?


In other words, if my row 5 looks like this:

1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 0 0 0 0 0 0 0

instead of this:

1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2 1 2

How can I get the correct average of just the 1's and 2's?

Thanks in advance

Mike