Thread: Dynamic range setting

This should be easy ... can't figure it out. If I want a formula that wants to perform an action (NPV, but I don't think that matters) on the cell directly above and the next "X" (say 20 cells/years of cash flow) to the right of it, how can I set it up so that I can copy the formula across as well as change "X".

(Obviously, it's easy to select a 20 cell range and copy it across ... but what if I want the length of the range to by dynamic (ie I want to switch to a 40 year NPV instead of a 20 year)?)

You can potentially use INDEX... let's simplify to a SUM

Say: row 1 contains numerics

A2: I enter no. of columns I wish to Sum from A onwards, eg 20

B2: =SUM(A1:INDEX(1:1,A2))

There is also the possibility of using OFFSET but unlike INDEX this is Volatile

B2: =SUM(OFFSET(A1,,,1,A2))