Relative Cell Reference


G

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)
 
Ad

Advertisements

G

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

---
 
Ad

Advertisements

R

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top