# First in first out (FIFO) subtraction

#### Nick G

##### New Member
Hello there,
I have an excel sheet which contains an inventory purchase & sell details of a stock.

In first column I have date in ascending order
In second column I have purchased stock quantity.
In last column I have sold stock quantity.

So what I want is that whenever there is a sell it should get deducted from the first purchase amount.
1-if the sale makes the purchase zero then the remaining sell amount should reduce the next purchase amount.
2- if the sell amount is not enough to reduce the purchase down to zero then the next sale should be deducted from that purchase untill it's zero and then move on to next purchase deduction from the remaining sell amounts

3. I want the formula added to should show how much of each purchase is remaining in stock.

Note:- 1)The data sheet I have got is static, i.e. there will be no more addition in purchase or sale in that data. Whatever I have got is final sheet.

2) before the first buy, the opening Balance is always zero.

Sample data

3/4/19 1500 200 0
3/5/19 2400 1400 0
4/17/19 400 2300 0
5/14/19 480 120 0
6/20/19 0 100 0
7/30/19 1003 1000 663
9/30/19 20 0 20
10/4/19 100 0 100
Total 5903 5120 783

Here the balance column is manually calculated as-
1500(first buy)-200(first sale) so still 1300 remains then out of 1400(next sale) that 1300 gets deducted and the balance column for the first purchase becomes zero.
The 100 amount of sell which is remaining from the 1400 sale then gets deducted from 2400 which is next purchase and then 2300 remains of that purchese so then 2300 of next sales gets deducted from that remaining purchase and second row of balance column becomes zero. And so on.

So what I want is that Balance column to be calculated by a formula or a vba or any other method.

I know this sounds complex. But kindly help me this as tha data that I get are in thounds of rows daily.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### Peter_SSs

##### MrExcel MVP, Moderator
Re: Help in First in first out (FIFO) subtraction

See if this does what you want.

Excel Workbook
ABCD
24/03/201915002000
35/03/2019240014000
417/04/201940023000
514/05/20194801200
620/06/201901000
730/07/201910031000663
830/09/201920020
94/10/20191000100
10Total59035120783
FIFO

#### Nick G

##### New Member
Re: Help in First in first out (FIFO) subtraction

It works like a charm. Thank you very much

#### Peter_SSs

##### MrExcel MVP, Moderator
Re: Help in First in first out (FIFO) subtraction

It works like a charm. Thank you very much
You're very welcome. Thanks for the follow-up.

##### New Member
I'm not sure if I can ask a follow-up question relating to above scenario but here goes. Instead of or possibly on top of the balance calculation, how do I populate (on another sheet or range) the following:
 Date. sell 4/3/2019 200 4/3/2019 1300 5/3/2019 100 5/3/2019 2300 17/04/2019 120 17/04/2019 100 17/04/2019 180 14/05/2019 480 30/07/2019 340

#### Peter_SSs

##### MrExcel MVP, Moderator
I'm not sure if I can ask a follow-up question relating to above scenario but here goes. Instead of or possibly on top of the balance calculation, how do I populate (on another sheet or range) the following
How (manually) do you arrive at those numbers?

Replies
4
Views
148
Replies
9
Views
323
Replies
2
Views
2K
Replies
0
Views
366
Replies
5
Views
127

1,141,060
Messages
5,704,039
Members
421,323
Latest member
Exidous

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back