# Expect Excel formula to find the product price by FIFO Method

#### Anbuselvam K

##### Member
Dear Excel Genius

I have produced some products in my company which is having or used different raw materials purchased with different prices. I want to calculate the products price per kg by using (Raw Materials) FIFO Method.

Below is the example data I have calculated Products 1 to 5's RM 1 price manually in column X, where I need to calculate RM 1 to 10 for the Products 1 to 5. (actual RM more than 100, just for an example I added 10 RM Only)

Products 1 to 5 RM Usage is in below image (Actual will be more than 10,000 productions are there to calculate) RM 1 to RM 10 Purchase qty with price is below Products 1 to 5 RM 1 cost manual entry is in below image (Attachment Column X) In the Attachment Column W, I need each products RM 1 to RM 10 Cost summation to be showed by excel formula. Attached the file for your reference.

We can split the data into different sheet (One sheet consumtion another sheet purchase data if required)
Also, You can add helper column and it can be hide too.
I want the expected results in Column W by one excel formula.

#### Attachments

• 27.5 KB Views: 7

#### Attachments

• 30.2 KB Views: 6
• • Anbuselvam K and deciog

#### Excel Wizard

##### Active Member

=SUM(TEXT(SUM(M\$4:M4)-MMULT(--(ROW(\$Z\$4:\$Z\$8)>TRANSPOSE(ROW(\$Z\$4:\$Z\$8))),--\$Z\$4:\$Z\$8),"0;\0")*(\$Y\$4:\$Y\$8-N(+\$Y\$3:\$Y\$7)))-SUM(W\$3:W3)

Ctrl+Shift+Enter

or Spill Array with MS365

=LET(q,M4:M8,p,Y4:Y8,s,Z4:Z8,sa,SCAN(0,s,LAMBDA(a,v,SUM(a,v)))-s,qa,SUM(q)-sa,
by,BYROW(SCAN(0,q,LAMBDA(a,v,a+v)),LAMBDA(v,LET(qa,v-sa,SUM(IF(qa>0,IF(qa>s,s,qa))*p)))),by-IFERROR(SMALL(by,SEQUENCE(ROWS(q),,0)),))

#### Attachments

• 29.2 KB Views: 4
Last edited:
• • Anbuselvam K and herofox

#### Anbuselvam K

##### Member
@Peter Bartholomew and @Excel Wizard
Thanks a ton to both of you!

I just downloaded and it seems both formulae are working fine. I am trying to get all the 10 RM costs in Column W.

Due to table formation, in the @Peter Bartholomew solution, I need some more time to understand and transfer it to my real sheet where I have 1000 products and more than 100 RM.

@Excel Wizard Solution I will transfer the excel formula to all RM and get one final answer in column W.

I am using MS365. But, I posted the question from MS Office 2007 version.

Once again thanks a lot for your effort towards my post.

#### Anbuselvam K

##### Member
=SUM(TEXT(SUM(M\$4:M4)-MMULT(--(ROW(\$Z\$4:\$Z\$8)>TRANSPOSE(ROW(\$Z\$4:\$Z\$8))),--\$Z\$4:\$Z\$8),"0;\0")*(\$Y\$4:\$Y\$8-N(+\$Y\$3:\$Y\$7)))-SUM(W\$3:W3)
The above formula I tried for RM 1 and transfer from RM 2 to RM 10, But for RM 6 and 7 there is an error. I couldn't track what is it? Could you please check and clear the error?

Tried column AU to BD. After this Calculation, I will transfer to the W column to get the final cost ( Sum of AU to BD) My Request as below
1) If possible please give me one excel formula to calculate and sum up the RM 1 to RM 10 to get the W column answer!
2) Actual sheet has more than 100 RM and 10,000 Products to calculate, So If I add 100 Extra columns with formula then the excel sheet will get slower to process each time.

#### Attachments

• 56.6 KB Views: 1

#### Anbuselvam K

##### Member
@Excel Wizard

I have applied both of your formulas and got some differences in the results. see below snapshot and the attached file for your kind consideration. The Red highlighted RM 5 Price is the wrong value by the first of your suggested formula.

The Green Highlighted RM 5 Price is correct when I compared it to the manual calculation which is your second suggested formula.

The manual calculation I added in column AH for RM 5.

#### Attachments

• 58.2 KB Views: 1

#### Anbuselvam K

##### Member
@Peter Bartholomew

I have observed and added the naming of RM2 to RM10 and I got the desired results in the sample file. But I don't know how to transfer the same to my actual file as it is having some kind of code inside the name manager. Sorry for my inability in the same.

Thanks again for the solution and the time spent on my post.

For Allocate I found the below.

LAMBDA(outputArray,inputArray,[weight],
LET(
cumulativeOutput, Accumulateλ(outputArray, 0, {0,1}),
cumulativeInput, Accumulateλ(inputArray, 0, {0,1}),
m, ROWS(outputArray),
n, ROWS(inputArray),
allocationArray, MAKEARRAY(
m,
n,
LAMBDA(k,h,
LET(
u, {-1,1} * INDEX(cumulativeOutput, k, {1,2}),
v, {-1,1} * INDEX(cumulativeInput, h, {1,2}),
d, SUM(IF(u < v, u, v)),
q, IF(d > 0, d, 0),
w, IF(ISOMITTED(weight), 1, INDEX(weight, h)),
q * w
)
)
),
IF(
ISOMITTED(weight),
allocationArray,
BYROW(allocationArray, Sumλ)
)
)
)

#### Attachments

• 33.7 KB Views: 1

#### Excel Wizard

##### Active Member
Try AY4
=SUM(TEXT(SUM(Q\$4:Q4)-MMULT(--(ROW(\$AH\$4:\$AH\$8)>TRANSPOSE(ROW(\$AH\$4:\$AH\$8))),--\$AH\$4:\$AH\$8),"0.00;\0")*(\$AG\$4:\$AG\$8-N(+\$AG\$3:\$AG\$7)))-SUM(AY\$3:AY3)

#### Attachments

• 58.3 KB Views: 2
• Anbuselvam K

#### Anbuselvam K

##### Member
Try AY4
=SUM(TEXT(SUM(Q\$4:Q4)-MMULT(--(ROW(\$AH\$4:\$AH\$8)>TRANSPOSE(ROW(\$AH\$4:\$AH\$8))),--\$AH\$4:\$AH\$8),"0.00;\0")*(\$AG\$4:\$AG\$8-N(+\$AG\$3:\$AG\$7)))-SUM(AY\$3:AY3)
Working perfectly.

Thanks a lot for your support.

#### Peter Bartholomew

##### Well-Known Member
This workbook contains a set of exploratory steps intended to lead to a solution. I wrote a set of lambda functions to access the data
Code:
``````RMConsumptionλ = LAMBDA(idx, INDEX(RMtbl_consumption, product_Index, idx));

RMPriceλ = LAMBDA(idx, INDEX(RMtbl_purchase, product_Index, 2 * idx - 1));

RMQuantityλ = LAMBDA(idx, INDEX(RMtbl_purchase, product_Index, 2 * idx));``````
and REDUCE to aggregate over RM indices.
Code:
``````=  REDUCE(0*product_Index, RM_Index,
LAMBDA(acc,idx, (acc + Allocateλ(RMConsumptionλ(idx), RMQuantityλ(idx), RMPriceλ(idx))))
)``````

#### Attachments

• 39.4 KB Views: 1
• Anbuselvam K

#### Peter Bartholomew

##### Well-Known Member
@Peter Bartholomew
...I don't know how to transfer the same to my actual file as it is having some kind of code inside the name manager. Sorry for my inability in the same.
You are perfectly correct that the 'code' is held within defined names. The apparent 'code' is simply worksheet formulas, but predominantly based upon very recent functions including Lambda. Even in Excel 2003, it was possible to write formulas and refer to them by name using Name Manager. It was, however, rarely done and the most familiar form was the Named Range where the formula just happens to be a range reference.

To transfer these complicated formulas from one workbook to another, one can copy a sheet or even just a formula from the first workbook to the second. The formulas will not work immediately because the range references to the active worksheet will no longer correspond to the data layout. In that situation, it is simply a case of working through the references one by one, changing the 'Refers to' property. I appreciate that modern Excel can be a major culture shock and hardly any users would yet claim to be fluent producing new solutions.

• Anbuselvam K

#### Anbuselvam K

##### Member
@Peter Bartholomew
Thank you so much for your post and the lambda function in detail. I will create the real data with an example set of data. then I will transfer the lambda to there. if I get stuck somewhere then I will ask the question by posting here.

Once again thanks for the lambda function which I am hearing for the first time and it's working perfectly.

#### Anbuselvam K

##### Member
=LET(q,M4:M8,p,Y4:Y8,s,Z4:Z8,sa,SCAN(0,s,LAMBDA(a,v,SUM(a,v)))-s,qa,SUM(q)-sa,
by,BYROW(SCAN(0,q,LAMBDA(a,v,a+v)),LAMBDA(v,LET(qa,v-sa,SUM(IF(qa>0,IF(qa>s,s,qa))*p)))),by-IFERROR(SMALL(by,SEQUENCE(ROWS(q),,0)),))
@Excel Wizard
I have transferred your suggested formula into my real working sheet where I have 34 RM (18 is Active) and above 40 products. all it is in Table format. I did some mistakes while transferring the formula. please see the below image and the attached file for the modification.

Expect the corrected formula as per tables and absolute reference to get the answer while adding the data in the tables. #### Attachments

• 96.4 KB Views: 5

#### Excel Wizard

##### Active Member
Try

=LET(q,Consumption[Consumption RM2],p,Purchase[Price RM2],s,Purchase[Qty RM2],sa,SCAN(0,s,LAMBDA(a,v,SUM(a,v)))-s,qa,SUM(q)-sa,
by,BYROW(SCAN(0,q,LAMBDA(a,v,a+v)),LAMBDA(v,LET(qa,v-sa,SUM(IF(qa>0,IF(qa>s,s,qa))*p)))),INDEX(by-IFERROR(SMALL(by,SEQUENCE(ROWS(q),,0)),),ROWS(CJ\$5:[@[Cost of RM2]])))

#### Attachments

• 92.4 KB Views: 9
• • Anbuselvam K and herofox