How to allocate in chronological sequence

[eshaft]

I'm building a sheet that shows the school fees (K-12) for my kids on a year by year basis. I want to be able to input my current savings balance and automatically see how far out that would pay for tuition and I'm trying to understand how to accomplish this.

For example: Assume school costs $100 this year, $200 in 2021, and $300 in 2022 and that I have $250 saved.

Current Savings: $250

2020 School Cost: $100
2020 School Currently Saved:$100

2021 School Cost: $200
2021 School Currently Saved:$150 (reflects the Current Savings minus the $100 that is already allocated for prior year)

2022 School Cost: $300
2022 School Currently Saved: $0 (this number would remain $0 until all prior years are fully allocated, at which point it would reflect the leftover savings amount)



Help is appreciate. Thanks in advance.

[alansidman]

Here is the set up I propose

v	A	B	C	D
1	Year	Current Savings	Tuition	Balance Savings
2	2020	250	100	150
3	2021	150	200	0
4	2022	0	300	0

Here are the formulas

v	A	B	C	D
1	Year	Current Savings	Tuition	Balance Savings
2	2020	250	100	=IF(B2-C2>0,B2-C2,0)
3	2021	=IF(D2<0,0,D2)	200	=IF(B3-C3>0,B3-C3,0)
4	2022	=IF(D3<0,0,D3)	300	=IF(B4-C4>0,B4-C4,0)

[eshaft]

Hi Alan,
Thanks for taking the time to help. It gets me the info I need, although not necessarily in the way I would like to see it. This just opens the door for me to learn more about Excel's functionality to fine tune it. Your comment already lead me down the rabbit hole of looking into various functions and playing around with them, so I'm sure I'll figure it out. Thanks again.

[alansidman]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.