I'm trying to simplify a cumulative expense calculation for financial modeling into one cell instead of creating a full waterfall. After lots of googling, I pieced together something that works using this formula:
=IF($R$3,SUM(OFFSET(O3,,,1,-MIN($R$3,COLUMNS($B$3:$P$3))))/$R$3,)
- Row 3 = annual expense
- Row 4: =IF($R$3,SUM(OFFSET(O3,,,1,-MIN($R$3,COLUMNS($B$3:$P$3))))/$R$3,), this calculates the cumulative expense for all previous years
- R3 = # of years to lookback
Workbook attached. To summarize: P4 needs to be the sum of the 20% (1/R3) of each of the previous R3 years of expense. So it will need to look-back from periods 10-14. And this lookback period is changed by the # of years. The current formula seems to work.
But I understand Offset is a volatile function and my workbook has really slowed down since starting using it (I re-enter/re-calculate thousands of cells many times). Is there a way to adjust this using Index/Match or some other formula?
It will need to handle changing the number of years in row R3
And preferably have some error checking built-in as not every calculation will have the full 15 periods of data. If not available, it will need to start at the first period which is greater than the # of years in in R3.
Bookmarks