Hi,
I'm making food cost calculations and would like to make my life more simple.
I have one sheet with the list of ingredients and their prices:
Screen Shot 2015-09-12 at 4.34.58 pm.png
On another sheet I have a pull-down of those ingredients and would like the cost (Column G)
to be calculated automatically based on the prices above, when I enter the weight in Column E
Screen Shot 2015-09-12 at 4.35.53 pm.png
Any Advice?
You can do some changes to link the price into the dropdown (in a second column) but to be honest I would just use a lookup table to look the price up based on the item youve selected.
Highlight your table of ingredients (including the prices) and give it a name (eg Ingredients) then in column G you would use the formula
which will look for the Ingredient youve selected in the Ingredients table and then return the 2nd column of that row before multiplying it by the quantity. What youve not said though is how the quantities are held in comparison to price for Lasagne Sheets you need 500g and calculate the cost at 12.00. is that a single 500g pack for 12.00 each or 5 100g packs? at 2.40 each?
=VLOOKUP(D2,Ingredients,2,False) * E2
What Im trying to say (poorly) is that if the quantity is 1,2,3 (units) etc then its straight forward but you dont seem to have any mention of what a unit consists of in your ingredients.
ps how big is this Lasagne? 2000 litres of milk???????
If someone has helped you then please add to their Reputation
hahaah 10 points for spotting the deliberate mistake.
BTW, All prices in the Ingredients sheet are per kg
Last edited by roger55; 09-12-2015 at 10:35 AM.
OK, I highlighted the Ingredients and Price Column, and defined it as Ingredients:
Screen Shot 2015-09-12 at 6.41.01 pm.png
Then I used your formula:
Screen Shot 2015-09-12 at 6.41.35 pm.png
As you see, getting #N/A
Is your list in alpha order?
On reflection it could be better to use INDEX and MATCH
MATCH will find the row that contains your data so if you have your Ingredients list in Columns A and B then use
to return the row that contains your item.=MATCH(D406,A:A,0)
Link this to INDEX and you get your price
which tells it to return Column B for the row you found in the match=INDEX(B:B,MATCH(D406,A:A,0),0,1)
that works very well !
now let me see if I can understand how exactly :/
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks