Calculating Averages: Part Deux

Is there a way to select a set of cells and determine the average for all cells that do not have 0 in them without a fat IF statement?

A1: 5
B1: 7
C1: 0
D1: 12
E1: 3

I'd like the average to be Sum(A1, C1, E1)/2 when the above example is true. If C1 changes to a number greater than 0, the average should be Sum(A1, C1, E1)/3.

Well an if statement makes it easier but

sum(a1:e1)/countif(a1:e1,"<>0")

regards

Dav

Thanks Dav but I wanted to sum specific cells and not the entire range. I'll probably go with an IF statement.

why If your exclusion is because the values are 0 if they are included the sum evaluates to the same number

sumif(a1:e1,"<>0")/countif(a1:e1,"<>0")

would be another solution, but is more complicated and will give you exactly the same answer.

Or you need to restate the question

Regards

Dav

The exclusion is simply that the cells I want to average are not adjacent. For example, in the attached image you will see totals for Shift 1, 2, and 3 and the last table is a combination of these three. I want the cells in the Combined Totals to be averages of the three shifts.

Does this image and explanation help?

Sorry. The image didn't attach... Hope this one works.

You do not have no 0's so

sumif(b1:h1,">0")/countif(b1:h1,">0")

Tell me if you have set the ranges correctly what is incorrect in the result, you have not been explict in the cells on the image you wish to be summed and averaged. If you are not you will not get a good answer, explain in english

Regards

Dav

There is no data at present because the image I sent is from a blank template. I apologize if it aded to the confusion. Perhaps the attached will be more helpful.

The "Combined Totals" table in this image includes the following rows:

A167: Bottle Change
A168: Cap Change
A169: Bot/Cap Change
A170: New Prod.
A171: Same Product/Bottle/Cap

I would like, for example, B167 to state "Look at cells B140, B149, B158 and give me an average of the sum of the cells that have a positive integer in them."

Does that provide you with a clearer picture? Again, my apologies for not being more clear to start with.