Recipe and product cost calculation help needed

[tugkan98]

Hello I need help with calculating price of a products from recipe list.
Screenshot_2.png

I have much bigger table with prices and recipes. I made a recursive macro to find prices of all but it works really slow and i need to improve it.
Do you know a way to find prices of given product from recipe and cost of recipe?
Thank you.

Recipe Cost.xlsx

[Limor_OP]

Hi,
Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[AliGW]

There is a workbook attached at the bottom of the opening post ...

[Shareez Saleem]

Please Login or Register  to view this content.

[Limor_OP]

Thanks Ali,

tugkan98 - what should the answer be in your example?

[tugkan98]

Answer is 19.465
Shareez's solution gives 3.1 as result

[Limor_OP]

please explain why 19.465

[AliGW]

I get 13.654 with this raw calculation; =2*C8+1*D14+1*Table5[@[used_amount]]+3*D12+3*D13

Is this the calculation you are wanting? If so, we can find a neat way of doing it.

No, wait - I think I get what you want, but I have to say, i don't think your chosen layout makes much sense.

Is this the raw calculation?

=2*C8+(1*D14*C6)+(1*Table5[@[used_amount]]*C7)+(3*D12*C4)+(3*D13*C5)

[torachan]

A single table approach.
Each recipe with individual ingredients.
The table is elastic, add another recipe to right hand column.
torchan

[p45cal]

I get 19.46 too, see cell F19 in the attached.
Now to try and figure how to get it more simply.

[tugkan98]

Screenshot_1.png

It is says its a link you cant post it so i share a screenshot

[AliGW]

See post #9.

[p45cal]

In the attached is a result table (pivot) at cell B25. Change values in either of the other 2 tables then right-click the reusult table and choose Refresh to update the result.
At the moment, because what's in the 2nd column of your table at cell B11 only needs to be looked up in the first column once (1 level of recursion) it works; if the lookup can happen more than once it will need a tweak.

[tugkan98]

Is there a formula which does recursion to the end of the node whatever the level of recursion is?
I wrote a working macro using recursive function but it took really long time because excel works slow and I wrote bad code.

[p45cal]

I'm sure it (Power Query) can but I have never tried to do it (yet).

[KokiKoki]

Try this at "FIND PRICE?"

=SUMPRODUCT(MMULT(--IF(IF(Table5[main_code]=[code],Table5[used_code],"")=TRANSPOSE(Table4[code]),TRANSPOSE(Table4[price])),SEQUENCE(COUNTA(Table4[code]),1,1,0)),IF(Table5[main_code]=[code],Table5[used_amount]))+SUM(MMULT(--(IF(IF(MMULT(IFERROR(FIND(Table5[main_code],TRANSPOSE(IF(Table5[main_code]=[code],Table5[used_code]))),0)*TRANSPOSE(Table5[used_amount]),SEQUENCE(COUNTA(Table5[main_code]),1,1,0)),Table5[used_code])=TRANSPOSE(Table4[code]),TRANSPOSE(Table4[price]))),SEQUENCE(COUNTA(Table4[code]),1,1,0))*MMULT(IFERROR(FIND(Table5[main_code],TRANSPOSE(IF(Table5[main_code]=[code],Table5[used_code]))),0)*TRANSPOSE(Table5[used_amount]),SEQUENCE(COUNTA(Table5[main_code]),1,1,0))*Table5[used_amount])