Recipe Calculator

[jem10660]

Hi Everyone

I'm trying to develop a recipe calculator linked to a product inventory like in the tables below, however my formula and function skills are limited but aspirational .

In the first table I want to list the ingredients that I buy, amount I bought, cost, price per unit. I then want to link the inventory table to a recipe calculator. I want to be able to type in a product from the inventory such as sugar in column e, the amount I used in the recipe in column f and in column g I want it to find the item from the first table and apply the price per unit from column d, multiply the amount used to give me the cost price in the recipe.

Can anyone help me with this? Its driving me insane.



[TABLE="width: 500"]
<tbody>[TR]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]sugar[/TD]
[TD]1000[/TD]
[TD]9.52[/TD]
[TD]0.00952[/TD]
[/TR]
[TR]
[TD]flour[/TD]
[TD]2000[/TD]
[TD]5.65[/TD]
[TD]0.002825[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[/TR]
[TR]
[TD]Product used[/TD]
[TD]amount[/TD]
[TD]cost[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Dryver14]

Sheet16

	A	B	C	D	E	F	G
Product	Total Buy	Cost	Cost per unit	Product Used	Amount used	Cost
sugar	sugar
Flour

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 86px;"><col style="width: 89px;"><col style="width: 90px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]9.52[/TD]
[TD="align: right"]0.00952[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]0.0476[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]5.65[/TD]
[TD="align: right"]0.002825[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G2 =VLOOKUP(E2,$A$2:$D$3,4,0)*F2 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[jem10660]

Thank you so much

 

[Dryver14]

No problem,obviously extend the lookup range according to your sheet

 

[jem10660]

Hello Me again

The formula is working great for items such as sugar, but if the item is more than one word such as tapioca starch is can't find the value. Is there anyway of accounting for this in the formula?

 

[Dryver14]

It should work for 2 words as long as they are in the same cell, the spelling needs to be identical

 

[Dryver14]

If you know how to do data validation that is the best way to go by creating a dropdown list of the names in the first column, that way you will always get the correct spelling

 

[jem10660]

It isn't working with two words but as you say they would have to be exact. I'll give the data validation dropdown a go. Thanks.