Microsoft Excel - A FIFO formula for mixed stocks and partial stock issue

Asked By Gauss on 22-Apr-14 04:25 AM
Dear Experts,

In my excel spreadsheet stocks in and out are entered as follows:
From Row 5
Col F Stock name
Col H Quantity
Col I Price
Col L Cumulative Qty
Col M Cumulative buy cost
Col K Cost(In this column is a formula to fill in cost of stock issued and brought in "=IF($H5>=0,H5*I5,-(MAX(IF($L5:$L5<-SUMIF($H5:$H5,"<0"),$M5:$M5))
it works perfectly but only for single stock variety, how do i modify it to work for different stocks?
At present i have to create many worksheets for each stock variety.
Thank you for your inputs.
Harry Boughen replied to Gauss on 22-Apr-14 08:40 AM
Hello Gauss,
I have to admit that I have only given this very little thought, but I suspect that what you are trying to do will not be able to be achieved by formula alone.  I suspect that you will have to use some VBA to do the calculations and update the relevant cells.  Posting a small sample of realistic data in a spreadsheet indicating inputs and desired outputs would help.
Gauss replied to Harry Boughen on 23-Apr-14 02:55 AM
Thank you for reply, please see attached file.
best regards.
Harry Boughen replied to Gauss on 23-Apr-14 03:59 AM
Hello Gauss,
I don't know if this will work but it gives the answers you want with the data that you give.

Enter into K5 and copy down.