Find the purchase cost from consumption cost

[Anbuselvam K]

Dear All

Find the attached workbook having the sheets of RM Consumption and RM Purchase. RM's are RM1 to RM51

In RM Consumption sheet I have RM Price and RM quantity, Whenever the RM Price changes I have highlighted by yellow colour.

I need the price in RM Purchase sheet, next to the purchase qty.

Example

RM Consumption Sheet
RM 1 consumed of 337687.93kg(Cell C6) with the price of 1.55 (Cell B2: B6) then up to 2203144.55kg(Cell C21) consumed with the price of 1.47(Cell B7: B21)

Expected Results
RM Purchase Sheet
Cell C11 value to be 1.55
Cell C16 value to be 1.55
Cell C17 value to be 1.55
Cell C18 value to be 1.55
....
....
....

up to the purchased quantity reaches 337687.93kg's the price should fill with 1.55 then it should be 1.47 up to 2203144.55kg's...

Up to Cell B11 to B41 the quantity reach 344510, So in the cell B41 must take average of 1.55 and 1.47 (FIFO method price)

Likewise RM1 to RM51 prices I need in the RM Purchase sheet price columns.

Hope someone will help here.

Thanks in advance.

 

[herofox]

you can to use this Array

=IFERROR(INDEX('RM Consumption'!$B$2:$CY$207,MATCH($A8,'RM Consumption'!$A$2:$A$207,0),) INDEX('RM Consumption'!$B$2:$CY$207,,MATCH(B$7,'RM Consumption'!$B$1:$CY$1,0)),"")

press Ctrl+Shift+Enter
i don't know every time ,what the problem with me when Upload The File ?!!!

 

[Anbuselvam K]

Dear Herofox

Check the attached file with your suggested formula. It is not showing the results.

Could you please attach the file with your formulation.

Thanks for your effort.

 

[Anbuselvam K]

Sorry, I match the heading of RM 1 Qty now for the checking.

Check the attached sheet. In that, I added your formula results as well as expected results in the next column manually. Also, I added the calculations for the FIFO for the cell D41.

Actually, we need to pick the cost of the RM instead of the quantity.

Hope you can change the formula accordingly.

Thanks in advance.

 

[herofox]

I DON'T Know about problem with Upload the file !!!!
I hope moderators and managers solve this problem
i Hope every think Now Clear for You

 

[Anbuselvam K]

Dear Herofox

If there is a problem with uploading, please share the Dropbox or Onedrive file link for the quick action.

Please note that, You are checking the dates and extract the prices accordingly. Actually we need to check the quantity consumption with the price changes, Also need to extract the price according to the quantity(FIFO Method)

Post #5 attachment is explained the expected results in D8: D40 and FIFO calculations in the Cells D3: K4 for the cell D41 results.

Please check again and do the needful.

Thanks in advance.