Automatically changing ranges in a formula based on assumption changes

[stevenmarcus1]

We have built a 5 year financial plan by month. We have cum columns such as year totals for each row. Example, revenues. We also have a cum column titled Cash Flow Positive (CFP) which is the sum of all revenues, spending, and EBITDA from inception to CFP (the last negative number on the EBITDA line). The current CFP occurs in month 30 and the column cums months 1 to 30.


If we change an assumption such as pricing, it will change the month of CFP. How do we get the CFP cum column to automatically update for the new range of months?

 

[C Moore]

The Offset function is the only way to set up dynamic formulas, because you essentially tell it what 'array' to use.