Excel – How to fill down formula so that range references are offset by 5 rows rather than by 1 row

microsoft excelmicrosoft-excel-2010worksheet-function

How can I get Excel to increment ranges arguments by more than one row when I fill down a formula?

For example, if I want Excel to keep averaging the next five numbers in a column, how do I set the average function to keep it in a pattern throughout, without changing it from an increment of one from the first and last number of the previous formula?

Excel keeps giving me this when I fill down:

=Average(A1:A5)
=Average(A2:A6)
=Average(A3:A7)

I want Excel to do this instead:

=Average(A1:A5)
=Average(A6:A10)
=Average(A11:A15)

How can I do this by filling down?

Best Answer

=AVERAGE(OFFSET($A$1,(ROW(A1) - 1) * 5,0,5,1))

I put this formula in B1, copied and pasted down column B.

It is performing an average on a range found using the offset formula.

OFFSET (REFERENCE, ROW OFFSET, COLUMN OFFSET, HEIGHT, WIDTH)
REFERENCE = Absolute reference to A1
ROW OFFSET = Current row number minus 1 times 5
COLUMN OFFSET = 0
HEIGHT = 5
WIDTH = 1

If you wanted to paste this across row 1 starting at B1 change to

=AVERAGE(OFFSET($A$1,(COLUMN(A1) - 1) * 5,0,5,1))