# 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 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.
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  