Hi all, i need help to understand the formulas in column P -> Column T. My goal is to perform first in, first out (FIFO) calculation, to show inventory price in the earliest purchase price.
Excel spreadsheet link:-
http://www.costviewer.com/files/CostViewer/Downloads/FIFO_model_for_many_products_no_VBA_v5.xlsx
Formulas
P19
SUM(--IF(MMULT(--(ROW(E$17:E18)>=TRANSPOSE(ROW(E$17:E18))),--IF(D$17:D18=H19,E$17:E18,0))<sumif(h$18:h19,h19,i$18:i19),1,0))
Q19
=SUMPRODUCT(--IF(OFFSET(D$17,,,P19+1)=H19,1,0),OFFSET(E$17,,,P19+1),OFFSET(G$17,,,P19+1))-SUMIF(H$18:H18,H19,T$18:T18)
R19
=(SUMIF(H$18:H19,H19,I$18:I19)-SUMPRODUCT(--IF(OFFSET(D$17,,,P19+1)=H19,1,0),OFFSET(E$17,,,P19+1)))*OFFSET(G$17,P19,,,)
T19
=IF(SUMIF(H$18:H19,H19,I$18:I19)>SUM(D$18:D19,H19,E$18:E19),MAX(SUMIF(D$18:D19,H19,F$18:F19)-SUMIF(H$18:H18,H19,J$18:J18),0),S19)</sumif(h$18:h19,h19,i$18:i19),1,0))
Excel spreadsheet link:-
http://www.costviewer.com/files/CostViewer/Downloads/FIFO_model_for_many_products_no_VBA_v5.xlsx
Formulas
P19
SUM(--IF(MMULT(--(ROW(E$17:E18)>=TRANSPOSE(ROW(E$17:E18))),--IF(D$17:D18=H19,E$17:E18,0))<sumif(h$18:h19,h19,i$18:i19),1,0))
Q19
=SUMPRODUCT(--IF(OFFSET(D$17,,,P19+1)=H19,1,0),OFFSET(E$17,,,P19+1),OFFSET(G$17,,,P19+1))-SUMIF(H$18:H18,H19,T$18:T18)
R19
=(SUMIF(H$18:H19,H19,I$18:I19)-SUMPRODUCT(--IF(OFFSET(D$17,,,P19+1)=H19,1,0),OFFSET(E$17,,,P19+1)))*OFFSET(G$17,P19,,,)
T19
=IF(SUMIF(H$18:H19,H19,I$18:I19)>SUM(D$18:D19,H19,E$18:E19),MAX(SUMIF(D$18:D19,H19,F$18:F19)-SUMIF(H$18:H18,H19,J$18:J18),0),S19)</sumif(h$18:h19,h19,i$18:i19),1,0))
