Forum Discussion
Find weighted average unit cost based on 1 fixed table and 1 dynamic sales table
Just to let people know, I have solved this formula!!
However, the formula is too long, perhaps now it is easier to understand what I wanted. So maybe some of the experts can dig in and simplify the formula as much as possible. Would really appreciate it!! 🙂🙂🙂
There are 2 formulas, in cell O2 and cell L2.
Formula in Cell O2:
=IF(OR(ISERROR(INDEX($O1:O$2,SUMPRODUCT(MAX(ROW($I1:I$2)*(I2=$I1:I$2))-1))),ISTEXT(INDEX($O1:O$2,SUMPRODUCT(MAX(ROW($I1:I$2)*(I2=$I1:I$2))-1)))),(IF(INDEX(Table3[IN VALUE],MATCH([STOCK ITEM],Table3[STOCK ITEM],0),1)<>0,INDEX(Table3[IN VALUE],MATCH([STOCK ITEM],Table3[STOCK ITEM],0),1)))-(IF(INDEX(Table3[COST],MATCH([STOCK ITEM],Table3[STOCK ITEM],0),1)<>0,INDEX(Table3[COST],MATCH([STOCK ITEM],Table3[STOCK ITEM],0),1))*(SUMIFS([STOCK OUT],[STOCK ITEM],I2,[SR. '#],">="&$G$2,[SR. '#],"<="&[@[SR. '#]])))+([@[STOCK IN VALUE]]),INDEX($O1:O$2,SUMPRODUCT(MAX(ROW($I1:I$2)*(I2=$I1:I$2))-1))-(INDEX($L1:L$2,SUMPRODUCT(MAX(ROW($I1:I$2)*(I2=$I1:I$2))-1)))*[@[STOCK OUT]]+([@[STOCK IN VALUE]]))
Formula in Cell L2
=IF([@[CLOSING STOCK VALUE]]>0,IFERROR(ROUND([@[CLOSING STOCK VALUE]]/[@[CLOSING STOCK BALANCE]],2),""),INDEX($L1:L$2,SUMPRODUCT(MAX(ROW($I1:I$2)*(I2=$I1:I$2))-1)))
Please find attached REVISED excel file which probably makes it clearer.
The results i would like to receive are in the yellow column, Column L.
Just to let people know, I have solved this formula!!
However, the formula is too long, perhaps now it is easier to understand what I wanted. So maybe some of the experts can dig in and simplify the formula as much as possible. Would really appreciate it!! 🙂🙂🙂
There are 2 formulas, in cell O2 and cell L2.
Formula in Cell O2:
=IF(OR(ISERROR(INDEX($O1:O$2,SUMPRODUCT(MAX(ROW($I1:I$2)*(I2=$I1:I$2))-1))),ISTEXT(INDEX($O1:O$2,SUMPRODUCT(MAX(ROW($I1:I$2)*(I2=$I1:I$2))-1)))),(IF(INDEX(Table3[IN VALUE],MATCH([STOCK ITEM],Table3[STOCK ITEM],0),1)<>0,INDEX(Table3[IN VALUE],MATCH([STOCK ITEM],Table3[STOCK ITEM],0),1)))-(IF(INDEX(Table3[COST],MATCH([STOCK ITEM],Table3[STOCK ITEM],0),1)<>0,INDEX(Table3[COST],MATCH([STOCK ITEM],Table3[STOCK ITEM],0),1))*(SUMIFS([STOCK OUT],[STOCK ITEM],I2,[SR. '#],">="&$G$2,[SR. '#],"<="&[@[SR. '#]])))+([@[STOCK IN VALUE]]),INDEX($O1:O$2,SUMPRODUCT(MAX(ROW($I1:I$2)*(I2=$I1:I$2))-1))-(INDEX($L1:L$2,SUMPRODUCT(MAX(ROW($I1:I$2)*(I2=$I1:I$2))-1)))*[@[STOCK OUT]]+([@[STOCK IN VALUE]]))
Formula in Cell L2
=IF([@[CLOSING STOCK VALUE]]>0,IFERROR(ROUND([@[CLOSING STOCK VALUE]]/[@[CLOSING STOCK BALANCE]],2),""),INDEX($L1:L$2,SUMPRODUCT(MAX(ROW($I1:I$2)*(I2=$I1:I$2))-1)))