Relative Cell Reference

[Guest]

The fill handle allows me to create a function in a cell (a1) and drag it to
cell (a2). With relative referencing the cells referenced in my function
will also move a single row down.

Is there a way to use relative referencing to move a single row down, but
change the function's reference by 5 units?

ie function in cell a1 is sum(b1:b5)
I want cell a2 to say sum(b6:b10)

all I seem to be able do is to get a2 to say sum(b2:b6)

 

[Guest]

ie function in cell a1 is sum(b1:b5)

I want cell a2 to say sum(b6:b10)

One way to achieve it ..

Put in A1:
=SUM(OFFSET(INDIRECT("B"&ROW(A1)*5-5+1),,,5))
Copy down

---

 

[Ragdyer]

And of course, the non-volatile alternative:

=SUM(INDEX(B:B,5*ROWS($1:1)-4):INDEX(B:B,5*ROWS($1:1)))

And copy down as needed.