formula to calculate averages of multiple ranges of cells

Hi there,

I wonder if you could help me I am trying to compose a formula that calculates the average of 3 different cell ranges. I have this formula so far

=IF(SUM(RANGE 1)=0,"",SUM(RANGE 1)/COUNTIF(RANGE 1,">0"))

It works great if I only have one range of cells, but when I add other ranges I keep getting an error message.

Please could you show me how to compose this formula so that it counts RANGEs 2 and RANGEs 3

Thanks

Sophy

Just use average; for eg:

=AVERAGE(A1:A4,A8:A12,A15:A17)

Will give the average of all the numbers in three different ranges

Hi,

You can use the following. Here I've named three ranges Range1, Range2, Range3 in Name Manager.

I'm assuming also that all values are your ranges have to be >=0? If yes, the following will work.

=IF(SUM(Range1) + SUM(Range2) + SUM(Range3) = 0, "", (SUM(Range1) + SUM(Range2) + SUM(Range3))/(COUNTIF(Range1, ">0") + COUNTIF(Range2, ">0") + COUNTIF(Range3, ">0")))

If, however, you have values that are < 0, you should instead do...

=IF(COUNTIF(Range1, "<>0") + COUNTIF(Range2, "<>0") + COUNTIF(Range3, "<>0") = 0, "", (SUM(Range1) + SUM(Range2) + SUM(Range3))/(COUNTIF(Range1, "<>0") + COUNTIF(Range2, "<>0") + COUNTIF(Range3, "<>0")))

since otherwise you could have a range of values that sum to 0 but all are non-zero. In the first example, the formula wouldn't calculate this, but in the second example, it would give the correct average of 0.

Is this what you were looking for?