Forum Discussion
Array formula reference prior column value
You wrote: My goal here is to minimize formulas and to use "#" formulas or range formulas like "C1:K1" as much as possible.
And I would like (in the role of a consultant you've brought into the project of projecting possible futures) to question that as the goal.
Wouldn't you want the goal to be to give yourself a model for projecting future fiscal year results, a model that contains some flexibility for a variety of assumptions concerning, oh, COLA adjustments, Economic growth at large, etc? With that (flexible modeling) as the goal, so far as Excel is concerned, you'd want to use the most appropriate tools, which would in that case include at the very least a table, perhaps some named ranges, in which you could place assumptions regarding COLA, economic growth/decline, interest, etc., and then see how various scenarios played out?
That's my contribution so far. If you'd be interested in pursuing that a bit, I'd be happy to offer an example.