How to fill down formula so that range references are offset by 5 rows rather than by 1 row [duplicate]
=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))