formula

[JohnnyGi]

So, Row 2 will have varying values in each cell (see the "Price" row). I want to be able to put a 1 or 2 or sometimes even a decimal in the cells across from the menu item (see the 1 entered under the burger patty column). Once I enter that 1 or 2 or whatever, I want Excel to multiply it by the number in Row 2. Any time a number is inserted across from the menu items I want this done and the products added together and appear in the "cost" column next to the menu item.

	COST	PRICE	D.I.P.	COST %	Burger Patty
Price					$0.89
Quantity/Portion					1/3 lb burger
Basic Burger		$8.75			1
Basic Burger, DBL		$10.75

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[madnes88]

What are you trying to total? Why do you have 'Price' on both the X and Y axis?

 

[JohnnyGi]

I'm trying to total "COST"... long story short, the cost will be made up of all the items associated with the menu item in the rows. I want to be able to put a 1 or 2 or sometimes a decimal under ingredient. For example, see that a burger patty costs 89 cents. if i put a 1 in the row for basic burger/column for burger patty... i want the cost to reflect this by multiplying my 1 times the 89 cents. i would put a 2 for a double burger, etc.

 

[jtakw]

Hi,

Is this what you mean?

Excel 2010
	A	B	C	D	E	F
1		COST	PRICE	D.I.P.	COST %	Burger Patty
2	Price					$0.89
3	Quantity/Portion					1/3 lb burger
4	Basic Burger	$0.89	$8.75			1
5	Basic Burger, DBL	$1.78	$10.75			2
Sheet1

Cell Formulas
Range	Formula
B4	=F$2*F4

B4 formula copied down.

 

[JohnnyGi]

yes, but the ingredients will expand past burger patty. for example, there will end up being 100+ ingredients columns, (burger patty, lettuce, etc) that will need to be summed together in the "cost"

 

[madnes88]

Hey JohnnyGi,

I think you are going about this a bit wrong. I used to be in food service and we did something similar. I think you need one table that lists your ingredients, and a separate table that calculates menu item cost.

 

[JohnnyGi]

there is another spreadhseet in the workbook linked to the ingredients pricing that you see (to get the 89 cents for the burger patty for example. we're so close. i just need the formula to extend the =F$2*F4... without having to do this =(F$2*F4)+(g$2*g4)+(h$2*h4)... which is essentially what i need calculated written out manually for many columns.

 

[jtakw]

yes, but the ingredients will expand past burger patty. for example, there will end up being 100+ ingredients columns, (burger patty, lettuce, etc) that will need to be summed together in the "cost"

How about this?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		COST	PRICE	D.I.P.	COST %	Burger Patty	Burger Bun	Lettuce	Tomato	Ketchup	Mustard	Pickles	Onion	Hot Dog Bun	Beef Franks	Relish
2	Price					$0.89	$0.25	$0.10	$0.20	$0.20	$0.10	$0.10	$0.10	$0.25	$0.75	$0.10
3	Quantity/Portion					1/3 lb burger
4	Basic Burger	$1.64	$8.75			1	1	1	1			1	1
5	Basic Burger, DBL	$3.03	$10.75			2	1	2	2			2	2
6	Hot Dog	$1.20									1			1	1	1
Sheet1

Cell Formulas
Range	Formula
B4	=SUMPRODUCT(--(F$2:P$2)*(F4:P4))

B4 formula copied down.

 

[madnes88]

What if you were to do something line this:

Item	Burger	Bun	Lettuce	Pickle	Tomato	Cost	Sale Price	DIP	Cost %
	.89	.12	1.19	.12	1.16
Basic burger	1	1	.1	.1	.5	Autosum

You could then tie the item pricing to the item cost table so you only have to update the item cost table and it will carry the price over. You would have to do the calculation once for your first line item, but could copy the autosum calc for the rest of the menu items. I am not sure how else to do the calculation without manually doing one item, unless you create a macro.