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))
-(SUMIF($H5:$H5,"<0")+MAX(IF($L5:$L5<-SUMIF($H5:$H5,"<0"),$L5:$L5)))
*INDEX($I5:$I5,MATCH(MIN(IF($L5:$L5>=-SUMIF($H5:$H5,"<0"),$L5:$L5)),$L5:$L5,0))
+SUMIF(OFFSET(K5,-1,0,-ROW(K5)+1,1),"<0")))",
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.
Gauss
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.
Regards
Harry
Gauss replied to Harry Boughen on 23-Apr-14 02:55 AM
Thank you for reply, please see attached file.
Stocks FIFO.zip
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.
=SUMPRODUCT(($F$5:$F$8=F5)*$I$5:$I$8)*H5

Regards
Harry