Subtract a value from a row above and repeat until 0

[klcrft78]

Hello,

I'm trying to figure out how I can change the quantity in A6 and have excel subtract from A5 until it reaches 0 then subtract from A4 until it reaches 0 etc.

Example - If I change A6 quantity to 400. Excel will change A5 to 0 and A4 to 100. I want excel to keep subtracting up the column so A1:A4 will equal the new total of 400.
Example - If I change the total to 350 then it would make A5 zero, then A4 zero then A3 fifty so A1:A3 would total 350.

Can you help? Thank you.

 

[klcrft78]

Sorry I can't add! See why I need your help? - If I change the total to 250 then it would make A5 zero, then A4 zero then A3 fifty so A1:A3 would total 250.

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this does what you want.

23 07 08.xlsm
	A
1	150
2	50
3	100
4	200
5	100
6	600
Subtract

Cell Formulas
Range	Formula
A1	A1	=MIN(A6,150)
A2	A2	=MIN(MAX(A$6-SUM(A$1:A1),0),50)
A3,A5	A3	=MIN(MAX(A$6-SUM(A$1:A2),0),100)
A4	A4	=MIN(MAX(A$6-SUM(A$1:A3),0),200)

 

[klcrft78]

I definitively think you are on the right track!

Having the quantity in the formula's (50,100,200) won't work because this just an example. The quantities could be any number so I just want to be able to change the total in A6 myself and have excel automatically reduce A5 to reach my new total - to zero if necessary. After reducing to zero it will go to A4 and reduce the quantity enough to reach the new total. Excel would work up the line until the totals in A1-A5 equal the new total.

Basically I need excel to work backwards in a way. Instead of excel giving me the total in A6 I give excel the NEW total in A6 and excel needs to work up reducing the quantities to total what I've I entered in A6.


I removed the quantities from the end of your formula changing it to =MIN(MAX(A$6-SUM(A$1:A4),0)) and it worked on A5 but when I copied to the above cells I got this in all cells "#REF!"

You are so close with this!!

 

[Peter_SSs]

I don't understand at all what you want then.
If it isn't written somewhere (in the formula or elsewhere in the worksheet) and I enter 1000 in A6, how do we know what value should be in A5?

 

[klcrft78]

I don't understand at all what you want then.
If it isn't written somewhere (in the formula or elsewhere in the worksheet) and I enter 1000 in A6, how do we know what value should be in A5?Yo

You are on the right track. I might be confusing you though.

If I enter a total new total in A6. (using my example) - Say 250 for example. Then excel will bring A5 down to 0 then A4 down to 0 then A3 down to 50. That way A1, A2 and the new balance of 50 in A3 total the new Total of 250 I keyed into A6.

Basically these are orders with the oldest at the bottom. When they ship some of the orders the counts need to decrease as they are shipped. A6 is the total of the amount of orders not shipped yet.

 

[Peter_SSs]

Say 250 for example. Then excel will bring A5 down to 0

Why will it become 0? What is the logic?

A cell cannot contain a value and a formula producing a different value. So if A5 contains a formula to "bring something down to 0" then it cannot already have a number in it (eg 100 in your original image)
That is why I built the 100 into the formula.

If you are happy to have the base values for each row stored in a helper column then you could do it like this.

23 07 08.xlsm
	A	B
1	150	150
2	50	50
3	100	100
4	200	200
5	100	100
6	600
Subtract

Cell Formulas
Range	Formula
A1	A1	=MIN(A6,B1)
A2	A2	=MIN(MAX(A$6-B1,0),B2)
A3:A5	A3	=MIN(MAX(A$6-SUM(B$1:B2),0),B3)

Change A6 to 250:

23 07 08.xlsm
	A	B
1	150	150
2	50	50
3	50	100
4	0	200
5	0	100
6	250
Subtract

Cell Formulas
Range	Formula
A1	A1	=MIN(A6,B1)
A2	A2	=MIN(MAX(A$6-B1,0),B2)
A3:A5	A3	=MIN(MAX(A$6-SUM(B$1:B2),0),B3)

 

[klcrft78]

Why will it become 0? What is the logic?

A cell cannot contain a value and a formula producing a different value. So if A5 contains a formula to "bring something down to 0" then it cannot already have a number in it (eg 100 in your original image)
That is why I built the 100 into the formula.

If you are happy to have the base values for each row stored in a helper column then you could do it like this.

23 07 08.xlsm
	A	B
1	150	150
2	50	50
3	100	100
4	200	200
5	100	100
6	600
Subtract

Cell Formulas
Range	Formula
A1	A1	=MIN(A6,B1)
A2	A2	=MIN(MAX(A$6-B1,0),B2)
A3:A5	A3	=MIN(MAX(A$6-SUM(B$1:B2),0),B3)

Change A6 to 250:

23 07 08.xlsm
	A	B
1	150	150
2	50	50
3	50	100
4	0	200
5	0	100
6	250
Subtract

Cell Formulas
Range	Formula
A1	A1	=MIN(A6,B1)
A2	A2	=MIN(MAX(A$6-B1,0),B2)
A3:A5	A3	=MIN(MAX(A$6-SUM(B$1:B2),0),B3)

ok I understand that. That's why I've been having issues. I guess i've been trying to have a number and formula in a cell.

There are many more lines on our actual spreadsheet with many different products. Not all product ship at the same time so having 0 in that cell shows this particalur item has shipped for that day but the other items for that day may have not shipped so they will still have quantities. Otherwise we could update by deleting the line but we can't do that until all items have reached 0 for that day (or on that line).

I thank you for your help! I can definitely use the information you have given me. Thank you!

 

[Peter_SSs]

You're welcome. Good luck with the rest of it.