Excel Formula -Simplifying formula

workrelated

New Member
Joined
Apr 29, 2018
Messages
3
Hi,

I need to simplify a formula, please refer to the screen below.

I will need help for row12 (E12, F12, G12, H12).

Is there any that i can do it in a single formula, instead of doing it manually ?

Thaks

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><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></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">Quantity</td><td style="font-weight: bold;;">Items</td><td style="font-weight: bold;border-right: 1px solid black;;">Price</td><td style="font-weight: bold;border-right: 1px solid black;border-left: 1px solid black;;">Single Price</td><td style="font-weight: bold;border-left: 1px solid black;;">Ben</td><td style="font-weight: bold;;">Aex</td><td style="font-weight: bold;;">Paul</td><td style="font-weight: bold;;">Smith</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">1</td><td style=";">Fried Chic Burger</td><td style="text-align: right;border-right: 1px solid black;;">18</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">18</td><td style="text-align: right;border-left: 1px solid black;;">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: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">1</td><td style=";">BBQ Chic Pizza</td><td style="text-align: right;border-right: 1px solid black;;">21</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">21</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">1</td><td style=";">Truffle Fries</td><td style="text-align: right;border-right: 1px solid black;;">15</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">15</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">2</td><td style=";">Spec Source C.Chop</td><td style="text-align: right;border-right: 1px solid black;;">32</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">16</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">3</td><td style=";">Salted Egg C.Chop</td><td style="text-align: right;border-right: 1px solid black;;">54</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">18</td><td style="text-align: right;border-left: 1px solid black;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">1</td><td style=";">Ice Lemon Tea</td><td style="text-align: right;border-right: 1px solid black;;">6</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">6</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">1</td><td style=";">Ice Honey Lemon</td><td style="text-align: right;border-right: 1px solid black;;">6</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">6</td><td style="text-align: right;border-left: 1px solid black;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">1</td><td style=";">Passion F Shake</td><td style="text-align: right;border-right: 1px solid black;;">13</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">13</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">1</td><td style=";">Glass</td><td style="text-align: right;border-right: 1px solid black;;">10</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">10</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">6</td><td style=";">Warm Sky Juice</td><td style="text-align: right;border-right: 1px solid black;;">6</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-left: 1px solid black;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style=";">Total</td><td style="text-align: right;border-right: 1px solid black;;">181</td><td style="text-align: right;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;background-color: #E2EFDA;;">44</td><td style="text-align: right;background-color: #E2EFDA;;">97</td><td style="text-align: right;background-color: #E2EFDA;;">29</td><td style="text-align: right;background-color: #E2EFDA;;">17</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">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: rgb(255,255,255)" ><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: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><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: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=C2/A2</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">=C3/A3</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D4</th><td style="text-align:left">=C4/A4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D5</th><td style="text-align:left">=C5/A5</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D6</th><td style="text-align:left">=C6/A6</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D7</th><td style="text-align:left">=C7/A7</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D8</th><td style="text-align:left">=C8/A8</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D9</th><td style="text-align:left">=C9/A9</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D10</th><td style="text-align:left">=C10/A10</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D11</th><td style="text-align:left">=C11/A11</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C12</th><td style="text-align:left">=SUM(<font color="Blue">C2:C11</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E12</th><td style="text-align:left">=E2*D2+E6*D6+E8*D8+E11*D11</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F12</th><td style="text-align:left">=F3*D3+F4*D4+F5*D5+F6*D6+F7*D7+F8*D8+F9*D9+F11*D11</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G12</th><td style="text-align:left">=G6*D6+G10*D10+G11*D11</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H12</th><td style="text-align:left">=H5*D5+H11*D11</td></tr></tbody></table></td></tr></table><br />
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
Judging from your post, you are doing it with formulas, not manually.

In E2 enter and copy across:

=SUMPRODUCT($C$2:$C$11/$A$2:$A$11,E2:E11)

which does not need D2:D11.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,075
Messages
5,526,667
Members
409,714
Latest member
diamondjoechubbs

This Week's Hot Topics

Top