Correct Google Sheets Autofill - Skipping Cells

I'm trying to have a spreadsheet with a second standings chart that references a lot of different information on the sheet; I'm trying to average a range of a row of cells, but the rows that have the numbers I'm using are 5 rows apart.

``````=AVERAGE(C3:Q3)
=AVERAGE(C9:Q9)
=AVERAGE(C15:Q15)
``````

This repeats for a while, but every time I try to autofill the rest of the cells with this formula, it never continues the pattern of skipping rows, and instead fills in rows between the gaps. How can I make the autofill follow the pattern I need it to? Is it possible?

Try calculating those averages with `sumif()` and `countif()`, and take only every sixth row with `query()`, like this:

``````=arrayformula(
query(
iferror(
sumif( if(column(C3:Q), row(C3:Q)), row(C3:Q), C3:Q )
/
(columns(C3:Q) - countif(C3:Q & "→" & row(C3:Q), "→" & row(C3:Q)))
),
"skipping 6", 0
)
)
``````

@doubleunary's answer is excellent and provides a single arrayformula to return all your values.

can I make the autofill follow the pattern I need it to?

Yes you can

The following formula can be auto-filled:

``````=IF(MOD(ROW()-3,6)=0,
AVERAGE(C3:Q3), IFERROR(1/0))
``````

Explanation

In order to work as you wish, the formula needs a way to perform one behavior on certain rows, and a different behavior on all others.

You can leverage the row numbers where you want the AVERAGE function applied because they follow a pattern.

They are all divisible by 6 without remainder after subtracting 3. For example if you subtract 3 from the row numbers 3, 9, 15, 21, you are left with 0, 6, 12, 18

Alternatively, if you subtract 3 from other row numbers like 4, 5, 6, 7, 8, 10, 11, 12, 13, 14, you are left with 1, 2, 3, 4, 5, 7, 8, 9, 10, 11.

MOD Function

The MOD function is ideally suited as its purpose is to return the remainder of a dividend and its divisor.
`MOD(dividend,divisor)`

So `MOD(ROW()-3,6)` will equal zero on all the rows where you want the calculation performed.

Using `MOD(ROW()-3,6)=0` as the test in an IF function will allow you to return your formula `AVERAGE(C3:Q3)` where it is TRUE or nothing if it is an in-between row and FALSE.

You could use a pair of double-quotes `""` for nothing but in Sheets `IFERROR(1/0)` is the way to properly return "nothing"