Google-sheets – Change ranges by several rows when copying a formula to another row

google sheets

I have a formula average(a3:a8) that I want to copy many times, changing the range as a block, i.e:

average(a3:a8)
average(a9:a14)
average(a15:a20)

The problem is, when I copy the formula to a new cell, it automatically updates the ranges to be:

average(a3:a8)
average(a4:a9)
average(a5:a10)

How can I do the former?

Best Answer

This requires some arithmetics with row numbers. I will suppose for example that your first formula is entered in D5. Enter the following in D5: 

=average(offset(A$3, 6*(row()-row(D$5)), 0, 6, 1))

Here row() is the current row. Since I've subtracted the row of D$5, the difference is zero so the expression is really offset(A$3, 0, 0, 6, 1). Offset means: from A3, move 0 rows down, 0 columns to the right, then take a rectangular range of 6 rows and 1 column. This describes the range A3:A8 and so its average is taken.

What happens if you copy the formula down, say to D6? Now row() has increased by 1, and so the row offset increased by 6. It became offset(A$3, 6, 0, 6, 1) which says: from A3, move 6 rows down, 0 columns to the right, then take a rectangular range of 6 rows and 1 column. This describes the range A9:A14 and so its average is taken.

Related Topic