Adjusting for a set of 7 columns

[rogmers]

I'm using the formula:
=IF(COUNT(Sheet2!C3:C9)=0,"",AVERAGE(Sheet2!C3:C9))

when I drag it down to copy in the columns below it gives me:
=IF(COUNT(Sheet2!C4:C10)=0,"",AVERAGE(Sheet2!C4:C10))

but I would like it to be:
=IF(COUNT(Sheet2!C10:C16)=0,"",AVERAGE(Sheet2!C10:C16))

Any advice on how I can make it automatically select the next set of 7 columns?

 

[shg]

=IFERROR(AVERAGE(INDEX(Sheet2!C:C, 7*ROWS(A$1:A1)-4):INDEX(Sheet2!C:C, 7*ROWS(A$1:A1)+2)), "")

Change the A1 (four places, leaving the $ sign intact) to the first cell in which the formula appears.