Forum Discussion
Array formula reference prior column value
I have a very common problem where i am trying to create projections by year in each column. The current column has to reference the prior column ending balance to create the projection for the current year and so on.
My goal here is to minimize formulas and to use "#" formulas or range formulas like "C1:K1" as much as possible.
I start out the calc with the starting year and ending balance as a starting point. Then I use a sequence formula for the years i want to calculate.
as you can see the Beginning and Ending Balance formulas dont work. I intentionally left the formulas that dont work to demonstrate what i would like to do. Any suggestions are appreciated.
1 Reply
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.
