Balance Remaining Formula

[tcorcoran15]

Hello All,

I am trying to create a formula to reduce sales from a specific purchase until that full purchase amount is then sold. I then want the formula to look at the next purchase and start to reduce that amount etc.

The sheet is similar to as follows:

On one tab ('Purchases') I have all the transactions pre-purchased like the below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Balance[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]500,000[/TD]
[TD="align: center"](Cell with formula)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]250,000[/TD]
[TD="align: center"](Cell with formula)[/TD]
[/TR]
</tbody>[/TABLE]


On another tab ('Product Sold') I have a list of all the sales against the purchases.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Reference[/TD]
[TD="align: center"]Amount[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]Alan[/TD]
[TD="align: center"]380,000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]Jack[/TD]
[TD="align: center"]120,000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: center"]Tom[/TD]
[TD="align: center"]180,000[/TD]
[/TR]
</tbody>[/TABLE]

As there has been 680,000 sold I would like cell B2 on the ('Purchases') tab to read '0' and cell B3 to read '70,000'.

Any help is massively appreciated

 

[AlanY]

will this work for you?

Excel 2013/2016
	A	B	C	D	E
1	Amount	Balance		Reference	Amount
2	500,000	0		Alan	380,000
3	250,000	70,000		Jack	120,000
4				Tom	180,000
Sheet2

Cell Formulas
Range	Formula
B2	=MAX(0,SUM($A$1:A2)-SUM(E:E))

 

[tcorcoran15]

Works great, thank you!

 

[AlanY]

you're welcome