# formula

#### JohnnyGi

##### New Member
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>

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
What are you trying to total? Why do you have 'Price' on both the X and Y axis?

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.

Hi,

Is this what you mean?

Excel 2010
ABCDEF
1COSTPRICED.I.P.COST %Burger Patty
2Price\$0.89
3Quantity/Portion1/3 lb burger
4Basic Burger\$0.89\$8.751
5Basic Burger, DBL\$1.78\$10.752
Sheet1
Cell Formulas
RangeFormula
B4=F\$2*F4

B4 formula copied down.

Last edited:
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"

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.

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.

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"

Excel 2010
ABCDEFGHIJKLMNOP
1COSTPRICED.I.P.COST %Burger PattyBurger BunLettuceTomatoKetchupMustardPicklesOnionHot Dog BunBeef FranksRelish
2Price\$0.89\$0.25\$0.10\$0.20\$0.20\$0.10\$0.10\$0.10\$0.25\$0.75\$0.10
3Quantity/Portion1/3 lb burger
4Basic Burger\$1.64\$8.75111111
5Basic Burger, DBL\$3.03\$10.75212222
6Hot Dog\$1.201111
Sheet1
Cell Formulas
RangeFormula
B4=SUMPRODUCT(--(F\$2:P\$2)*(F4:P4))

B4 formula copied down.

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.

Replies
2
Views
170
Replies
6
Views
272
Replies
2
Views
371
Replies
11
Views
460
Replies
6
Views
505

1,216,496
Messages
6,130,983
Members
449,611
Latest member
Bushra

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back