Formula that allows overpayment to flow to a different cell

[emma1204]

I am needing help developing a formula that allows the overflow from one to apply to another cell. Basically someone is paying multiple monthly debts but the second debt can't be satisfied until the first one is and the third one can't be satisfied until the second one is. So this is a monthly payment and the likelihood they would EVER exceed the total of all the debts is slim to none so if they did I would just apply that amount to the total paid for the following month. Thank you for your help.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD][/TD]
[TD]Owes[/TD]
[TD]Paid[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Amount Paid[/TD]
[TD="align: center"]n/a[/TD]
[TD]1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]1st debt[/TD]
[TD]800[/TD]
[TD]=C2-B3 BUT I need this to display a number that doesn't go past 800. So if they paid 1000 it will show 800[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]2nd debt[/TD]
[TD]500[/TD]
[TD]=B4-whatever is leftover from the above cell and the number doesn't go past 500. So if they paid enough to cover all the debt in B3 AND all the debt in B4 then it would show 500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]3rd debt[/TD]
[TD]100[/TD]
[TD]=B5-whatever is leftover from the above cell and the number doesn't go past 100.[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[brmeeke]

Hi

Hope that I am understanding your problem correctly. Here is a suggestions:

Formula for C3: =IF(C3>=B4, B4, C3)
Formula for C4: =IF(C3-C4>B5, B5, C3-C4)
Formula for C5: =IF(C3-C4-C5>0, C3-C4-C5, 0)

Hope that helps

Brian

 

[Fluff]

Maybe

Excel 2013/2016
	A	B	C
1		Owes	Paid
2	Amount Paid	n/a	1350
3	1st debt	800	800
4	2nd debt	500	500
5	3rd debt	100	50
Sheet3

Cell Formulas
Range	Formula
C3	=MIN(B3,C2)
C4	=MIN(B4,$C$2-SUM($C$3:C3))
C5	=MIN(B5,$C$2-SUM($C$3:C4))