formula

JohnnyGi

New Member
Joined
Feb 1, 2013
Messages
16
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.

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

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

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

madnes88

Board Regular
Joined
Nov 13, 2013
Messages
59
What are you trying to total? Why do you have 'Price' on both the X and Y axis?
 

JohnnyGi

New Member
Joined
Feb 1, 2013
Messages
16
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

Well-known Member
Joined
Jun 29, 2014
Messages
5,381
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Is this what you mean?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">COST</td><td style=";">PRICE</td><td style=";">D.I.P.</td><td style=";">COST %</td><td style=";">Burger Patty</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Price</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$0.89 </td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Quantity/Portion</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">1/3 lb burger</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Basic Burger</td><td style="text-align: right;;">$0.89 </td><td style="text-align: right;;">$8.75 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Basic Burger, DBL</td><td style="text-align: right;;">$1.78 </td><td style="text-align: right;;">$10.75 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B4</th><td style="text-align:left">=F$2*F4</td></tr></tbody></table></td></tr></table><br />

B4 formula copied down.
 
Last edited:

JohnnyGi

New Member
Joined
Feb 1, 2013
Messages
16

ADVERTISEMENT

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

Board Regular
Joined
Nov 13, 2013
Messages
59
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

New Member
Joined
Feb 1, 2013
Messages
16

ADVERTISEMENT

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

Well-known Member
Joined
Jun 29, 2014
Messages
5,381
Office Version
  1. 2016
Platform
  1. Windows
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?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">COST</td><td style=";">PRICE</td><td style=";">D.I.P.</td><td style=";">COST %</td><td style=";">Burger Patty</td><td style=";">Burger Bun</td><td style=";">Lettuce</td><td style=";">Tomato</td><td style=";">Ketchup</td><td style=";">Mustard</td><td style=";">Pickles</td><td style=";">Onion</td><td style=";">Hot Dog Bun</td><td style=";">Beef Franks</td><td style=";">Relish</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Price</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">$0.89 </td><td style="text-align: right;;">$0.25</td><td style="text-align: right;;">$0.10</td><td style="text-align: right;;">$0.20</td><td style="text-align: right;;">$0.20</td><td style="text-align: right;;">$0.10</td><td style="text-align: right;;">$0.10</td><td style="text-align: right;;">$0.10</td><td style="text-align: right;;">$0.25</td><td style="text-align: right;;">$0.75</td><td style="text-align: right;;">$0.10</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Quantity/Portion</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">1/3 lb burger</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Basic Burger</td><td style="text-align: right;;">$1.64</td><td style="text-align: right;;">$8.75 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Basic Burger, DBL</td><td style="text-align: right;;">$3.03</td><td style="text-align: right;;">$10.75 </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Hot Dog</td><td style="text-align: right;;">$1.20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">F$2:P$2</font>)*(<font color="Red">F4:P4</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

B4 formula copied down.
 

madnes88

Board Regular
Joined
Nov 13, 2013
Messages
59
What if you were to do something line this:

ItemBurgerBunLettucePickleTomatoCostSale PriceDIPCost %
.89.121.19.121.16
Basic burger11.1.1.5Autosum

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,968
Members
414,115
Latest member
SFUser

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?

Disable AdBlock

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
Top