Subtract from one cell until it reaches "0" before moving on to another cell

[HelpExcelHelpers]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]175[/TD]
[TD="align: center"]125[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]125[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]175[/TD]
[TD="align: center"]125[/TD]
[/TR]
</tbody>[/TABLE]

ROW ONE: When entering a value in L1, I would like the amount to deduct from I1 until it reaches zero before deducting from J1. M1 is the balance for the row

ROW TWO: Is how it should look if the formula is correct.

 

[footoo]

Enter in I2 and drag to K2 : =IF(I1=0,0,MAX(0,SUM($I1:I1)-$L1))

 

[HelpExcelHelpers]

It's not working

Here is the formula in I2:

=if(N1="",0,MAX(0,J1-L2))

Here is the formula I have in J2:
=MAX(180-K1)-L10

The problem I am having is when I enter data a value in L2, both I and J deduct at the same time instead of I first then J.

 

[shknbk2]

Are the values in J1 and K1 always 100 and 200? If not, do they come from some other cell on the page?
Is M supposed to be J1+K1? So, if nothing is in L1, M1 is 300?

Also, if L1 is then later changed to 125, does K1 go to 0 or does it go to 175 (i.e., the calculation starts over: J1 would be 0, and the remaining 25 subtracted from 200)

 

[shknbk2]

I meant to say Is M supposed to be I1+J1?

 

[HelpExcelHelpers]

Are the values in J1 and K1 always 100 and 200? If not, do they come from some other cell on the page?

Each row is a year so the values in I, J, and K do change based on the previous year. However, J always starts with 180.

Is M supposed to be J1+K1? So, if nothing is in L1, M1 is 300?

M is the total for the row. So it is I+J+K-L= (yes, you are correct that M1 would be 300 if no value is in L1)

Also, if L1 is then later changed to 125, does K1 go to 0 or does it go to 175 (i.e., the calculation starts over: J1 would be 0, and the remaining 25 subtracted from 200)

If L1 is 125, then it would take all of I1 making it "0" and THEN take only 25 from J1 (leaving 175 shown in J1).

 

[shknbk2]

Here's a start at what i have for the row. I put 100 and 200 in I1 and J1, but you will probably have to revise them to refer to being based on the previous year. I didn't do anything with K because I don't know what you wanted for that.

	I	J	K	L	M

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"]300[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I1[/TH]
[TD="align: left"]=IF(L1>=100,0,100-L1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J1[/TH]
[TD="align: left"]=IF(I1=0,200+100-L1,200)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]M1[/TH]
[TD="align: left"]=I1+J1+K1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[footoo]

It's not working

Here is the formula in I2:

=if(N1="",0,MAX(0,J1-L2))

Here is the formula I have in J2:
=MAX(180-K1)-L10

The problem I am having is when I enter data a value in L2, both I and J deduct at the same time instead of I first then J.

Why don't you try the formula I suggested?

 

[HelpExcelHelpers]

Year[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]4/3/18
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]120[/TD]
[TD="align: center"]180[/TD]
[TD="align: center"]30[/TD]
[TD="align: center"]220[/TD]
[TD="align: center"]110[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]50[/TD]
[TD="align: center"]100[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

ROW ONE:
J1 is the amount that will carry over to I2 ONLY if I enter a date in N1 so the formula I have for that is: =if(N1="",0,MAX(0,J1-L2)). However, if I don't put a date in N1 then I2 should say "0". Since I entered a date in N1, I2 has the amount of J1 carried over.
M1 is the total for the row (I+J+K-L=)
N1 is where I add a date IF I need the amount in J1 to carry over to I2.

ROW TWO:
I2 is the amount carried from J1 because I entered a date in N1.
J2 will always start with 180 but if I enter a value in K1(previous K cell), then J2 will deduct this amount from the initial 180. J2 says 180 because there is nothing to subtract from K1.
K2 says 30 which means that in J3 it'll say 150 instead of 180 (180-30=150)
L2 is where I would add a value. L2 will need to deduct I2 first before deducting J2 so in this example, I2 will now say "0", J2 will now say "80".
M2 would be the total for the row I2+J2+K2=330-L2=110

ROW THREE:
I3 is "0" because I did not enter a date in N2 so J2 (80 left) will not carry over.
J3 is 150 because we subtracted the amount from in K2 (180-30=150)
K3 no value
L3 has 50 so it should ONLY deduct from J3 since I3 has no value making J3 now 100.
M3 is again the total for the row like above.

Is there a way to incorporate the J formula =if(N1="",0,MAX(0,J1-L2)) to the one you gave above so that it does deduct correctly?

 

[HelpExcelHelpers]

Why don't you try the formula I suggested?

I did ... it didn't work because I didn't explain myself very well. UGH! I explained it in more detail above.