Average for Multiple Values in Multiple Columns

[DarbyBrown]

I have a sheet with 25 columns, the active area is C6 thru U12. I need to total the Values in every 5^th Column (ie:C6-C12,H6-H12,M6-M12 and R6-R12) that are >0 and then get the average for the total value. Here is what I have now, the formula being:

=AVERAGE(IF(MOD(ROW(C6:C12),5=1,C6:C12,””))

C D E F G H I J K L M N O P Q R
6 116 94 118 202
7 110 120 119 140
8 196 0 0 0
9 0 0 0 0
10 0 0 0 0
11 0 0 0 0
12 0 0 0 0

(T tried to include a screenshot of the sheet but it wouldn’t let me)

The result from the above formula shows as 141, and if you total all the numbers >0 it totals 1,215 and the cell Count is 9 so the Average should be 135. Where am I going wrong?

Sorry for the rudimentary drawing, but haven’t figured out how to include a screenshot.

Darby
When you were born, you cried and the world rejoiced.
Live your life in such a manner that when you die the world cries and you rejoice.
~Native American Proverb

 

[arthurbr]

Perhaps
<code>=AVERAGE(IF((MOD(COLUMN($C$6:$U$12)+2,5)=0)*($C$6:$U$12<>0),$C$6:$U$12))
Committed with Ctrl+Shift+Enter
</code>

 

[DarbyBrown]

That worked, I also came up with an alternative, but yours is much shorter and less confusing.

=AVERAGEA(AVERAGEIF($C$6:$C$12,">0"),AVERAGEIF($H$6:$H$12,">0"),AVERAGEIF($M$6:$M$12,">0"),AVERAGEIF($R$6:$R$12,">0"))

Thanks for the reply,

 

[arthurbr]

You're welcome