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:

	A	B
1	Amount	Balance
2	500,000	(Cell with formula)
3	250,000	(Cell with formula)

<tbody>
</tbody>

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

	A	B
1	Reference	Amount
2	Alan	380,000
3	Jack	120,000
4	Tom	180,000

<tbody>
</tbody>

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