Excel help

Solved/Closed
donkeyoaty - Feb 23, 2010 at 10:52 AM
 donkeyoaty - Feb 24, 2010 at 06:05 AM
Hello,

I am having trouble with an excel calculation, i'd be grateful for any help:

I have menu items listed along the top, with a space for the unit cost below
& the ingredients of the menu down the side with the size of packs alongside that
I will be filling the amount of ingredient used to make that menu item and I want the calculation to go down that list and where there has been a value entered I want it to add up the ingredient prices that go to make that menu item??!!!! Bit confusing I'll try and show you:


MENU ITEMS
coffee tea cheese sandwich
INGREDIENT PACK SIZE WEIGHTg COST unit cost £0.00 £0.00 £0.00
bread 1 x 200 £1.00 100
cheese 1 x 1000 £2.00 60
coffee 1 x 1000 £10.00 50
tea bag 100 x 20 £4.00 50
onion 1 x 5000 £2.00 20


These are just examples but you can see that to make a coffee here i need 50g of coffee, i would like the calculation to go down the list of ingredients and where it meets a value calculate the cost of (in this case 50g of coffee) and return it to the unit cost (currently showing £0.00) at the top
Obviously this gets more complicated for a multi ingredient item, like the cheese sandwich, is there a way it could calculate 100g of bread, 60g of cheese and 20g of onion and place the sum of them in the unit cost at the top?
Please be aware this is a simplified example and the figures are not correct, in reality there may be 10 ingredients in one menu item so i'll need it to calculate them all and then add them up.

Would i be better having excel first calculate the price of 1g of each ingredient then put that figure elsewhere and then refer back to that when doing calculations or is there a real easy way?

Thanks for your advice.

Matthew

1 reply

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Feb 23, 2010 at 10:58 AM
Ok I am just being lazy here, but could you possible put a sample file at some share site ? example
https://authentification.site
0
i'll do that right away, the message came out rubbish anyhoo, so thanks for the advice and i'll get right on it
0
Heres the file "menu" if you have any questions feel free to ask, and many thanks
Again this is a sample so you can get the idea

https://authentification.site/files/21097502/menu.xls
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767 > donkeyoaty
Feb 23, 2010 at 11:44 AM
Ok I am a bit of a intellectually challenged person so bear with me

I am reading your sheet as

1. I10 (300) and I24 (200) are the units that are needed to make chicken baguette (I3)

2. You want to find out that cost of
a. FRENCH STICK as
Cost = 8.90
weight = 2500 gm
pack size =1
so per unit cost is 8.90 / (2500 * 1) = 0.00356
Unit needed = 300
cost = 300 * 0.00356 = 1.068

b. CHICKEN PIECES (lets say pack size is 2)
Cost = 8.90
weight = 2500 gm
pack size =2 (lets presume it is so)
per unit cost is 8.90 / (2500 * 2) = 0.0.00178
Unit needed = 200
cost = 200 * 0.00178 =0.356

And what you need to know is how much it is total . ( 1.068 + 0.356)

Now set me straight. I am sure I am misreading your file.
0
donkeyoaty > rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Feb 23, 2010 at 03:40 PM
Hi thanks for looking, yes thats pretty much all i need to do, however other than using a long winded formula that checks each individual ingredient, given there will be dozens and dozens for each individual menu item, i need a formula that quickly does the calculations you have shown every time there is a value in that menu item

Is there a formula i can use that will do exactly what you have shown and calculate, even if there were 10 or more ingredients and brings back the sum of all those calculations?

Thanks once again
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767 > donkeyoaty
Feb 23, 2010 at 03:53 PM
I have a feeling some how sumproduct would be needed. Here is a question for you. What instead of a dynamic formula, you run a macro. So if you update some thing, just run the macro to re-evaluate things for you.
0