3

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?

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.