Find weighted average unit cost based on 1 fixed table and 1 dynamic sales table

[AJ1991]

Dear All,

Hope this finds you well.

I am having a hard time trying to figure out how to get the weighted average unit cost of each product when using two tables.

One table is fixed, which is the opening balance of inventory, and the other one is inventory movement according to each sales transaction.

I would like to fill the blue column under cost with a formula that gives us the unit cost per product according to the value of the existing inventory at the moment.

The blue table is the fixed one and the orange one is the dynamic table based on daily sales.

Eventually, the inventory in the blue table should be totally consumed ( hopefully :) ) and the unit cost should be according to the running stock balance.

Thanks in advance!!

[#2]

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.

[#3 · Accepted solution]

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)))