Formula needed to calculate inventory

[RSDB]

Is there a formula that will deduct an amount from a total in one column but when it reaches zero, the remaining amount gets deducted from another column so that there is not a negative amount showing. For example. There are 3 items left from the first stock order (column 1) and 20 items left from a the second stock order (column 2). A client as for delivery of 10 pieces of the item. 3 out of the 10 pieces get deducted from the first stock order, bringing the total to zero, no more stock left (column 1) and the remaining 7 items get deducted from the second stock order (column 2).

 

[KRice]

Welcome to the Board! Yes, you could determine the apportionment between columns several ways. Here is one example using SUBTOTAL, SUM, and MIN functions to draw some "total to deduct" from 3 columns. You can copy this entire example to your clipboard by clicking on the clipboard icon in the upper left (near intersection of row and column headers) and then pasting into a practice sheet in cell A1 of your workbook. Enter some value in B1 and the formula will apportion that amount between columns so as to not exceed the sum of that column.

Book2
	A	B	C	D
1	Total to deduct	23
2	Apportionment	18	5	0
3
4	Inventory
5	sum below -->	18	15	6
6		col1	col2	col3
7		3	3	2
8		4	5	4
9		5	7
10		6
11
Sheet2

Cell Formulas
Range	Formula
B2	B2	=MIN($B$1,B$5)
C2:D2	C2	=MIN($B$1-SUM($B2:B2),C$5)
B5:D5	B5	=SUBTOTAL(9,B7:B14)

 

[RSDB]

Thank you for your reply and your time. I have attached a screenshot of my worksheet. We delivered 6 items (I8), we had 10pcs in stock (D8) from two combined orders (B8+C8). The first image show with my current formula which is just a normal =I8-D8 calculation shows after 6pcs was delivered a negative amount. Im looking for a formula that will give me an answer as seen in the second image. J8=0, K8=4.

 

[KRice]

What do the rows represent? Your example discusses row 8. Is row 9 for an entirely different item or is it related to row 8 somehow?

 

[KRice]

You can adapt my earlier example like this, assuming the rows are independent:

Book2
	A	B	C	D	E	F	G
1		SO1	SO2	Inventory Sum	Delivery	Inventory SO1	Inventory SO2
2
3
4
5
6
7
8		5	5	10	6	5	1
9		35	5	40	38	35	3
10		37	13	50	47	37	10
Sheet3

Cell Formulas
Range	Formula
F8:F10	F8	=MIN($E8,B8)
G8:G10	G8	=MIN($E8-SUM($F8:F8),C8)
D8:D10	D8	=SUM(B8:C8)

 

[RSDB]

Yes each row is independent. Are there a way that it can show the remaining stock rather then what stock has been delivered. Referring to your F8 and G8.

 

[KRice]

Try something like this:

Book2
	A	B	C	D	E	F	G
1		SO1	SO2	Inventory Sum	Delivery	Inventory SO1	Inventory SO2
2
3
4
5
6
7
8		5	5	10	7	0	3
9		35	5	40	38	0	2
10		37	13	50	35	2	13
Sheet3

Cell Formulas
Range	Formula
F8:F10	F8	=B8-MIN($E8,B8)
G8:G10	G8	=C8-MIN($E8-(B8-F8),C8)
D8:D10	D8	=SUM(B8:C8)

 

[RSDB]

You are amazing. Thanks. I'll give this a try.