Offset from each nth cell with dynamic anchor cell

Question

I have hard times understanding Excel formula for Offset.

How I can start my range from 1 in this case, so it is 1, 3, 5 etc. At the moment it goes 3, 5, 7.

Also my offset formula should be dynamic. I mean I have tried something like =OFFSET($W$619;(ROW()-630)*2;0) but in this case it is not dynamic, so if I add rows on top, row number is not anymore 630 and formula is not working.

Answer 1

You are close with your solution. The dynamic can be achieved when you combine the OFFSET function with the ROWS function, which counts the number of rows between two cell references.

You also have to subtract 2 since you do not want to step down two rows in the first cell.

The final formula is:

=OFFSET($W$619;ROWS($W$619:W619)*2-2;)