reverse-percentage-calculation-on-percentage-fee

ykamal

New Member
Joined
Jul 30, 2019
Messages
11
I have a calculation Problem In My Application In which I need to Calculate Reverse Amount From Grand Total Amount. I Need Logic/Formula for It Need you guys Help.
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;"> I have Amount = 100
10 % Commission on Amount = 10
Fee On Amount = 1
Delivery Charges = 6
Tax I Flat = 1.50
Tax II 10 % On Fee (Commission + Free + Delivery Charges) = 1.70
------------------------------------------------------------------
Grand Total = 120.20
------------------------------------------------------------------
</code>Now I just want its Reverse Formula To Get Amount 100 From Grand Total 120.20. I Have Following Values;
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;"> Grand Total Amount = 120.20
Tax I Flat = 1.50
Fee On Amount = 1
Delivery Charges = 6
10 % Commission on Amount = ??
Tax II 10 % On Fee (Commission + Free + Delivery Charges) = ??
</code>Now if we have the amount which is (100) then we can find all the stuff please need your help to find the formula ??
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,822
Case 1:

<b></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 /><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><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Commission</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Amount</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">100.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Grand Total</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Amount</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">100.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Fee</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">1.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Commission</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">128.70</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Commission</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Delivery</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">6.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Fee</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">1.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Fee</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">1.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tax I Flat</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Delivery</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">6.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Delivery</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">6.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tax II %</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tax I</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">1.70</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tax I</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">1.70</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tax II</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tax II</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Grand Total</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">128.70</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Grand Total</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">128.70</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)">E2</th><td style="text-align:left">=E1*Commission</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E3</th><td style="text-align:left">=Fee</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E4</th><td style="text-align:left">=Delivery</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E5</th><td style="text-align:left">=TaxI*SUM(<font color="Blue">E2:E4</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E6</th><td style="text-align:left">=TaxII*E1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I1</th><td style="text-align:left">=(<font color="Blue">G2-(<font color="Red">TaxI+1</font>)*(<font color="Red">Fee+Delivery</font>)</font>)/(<font color="Blue">1+Commission+Commission*TaxI+TaxII</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">=I1*Commission</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I3</th><td style="text-align:left">=Fee</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I4</th><td style="text-align:left">=Delivery</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I5</th><td style="text-align:left">=TaxI*SUM(<font color="Blue">I2:I4</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I6</th><td style="text-align:left">=TaxII*I1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E8</th><td style="text-align:left">=SUM(<font color="Blue">E1:E6</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I8</th><td style="text-align:left">=SUM(<font color="Blue">I1:I6</font>)</td></tr></tbody></table></td></tr></table><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>Workbook Defined Names</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">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Commission</th><td style="text-align:left">=Sheet1!$B$1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Delivery</th><td style="text-align:left">=Sheet1!$B$3</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Fee</th><td style="text-align:left">=Sheet1!$B$2</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">TaxI</th><td style="text-align:left">=Sheet1!$B$4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">TaxII</th><td style="text-align:left">=Sheet1!$B$5</td></tr></tbody></table></td></tr></table><br />


Case 2:

<b></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 /><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><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Commission</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Amount</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">100.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Grand Total</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Amount</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">100.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Fee</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">1.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Commission</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">130.40</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Commission</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Delivery</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">6.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Fee</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">1.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Fee</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">1.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tax I Flat</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Delivery</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">6.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Delivery</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">6.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tax II %</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tax I</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">1.70</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tax I</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">1.70</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tax II</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">11.70</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tax II</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">11.70</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Grand Total</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">130.40</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Grand Total</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">130.40</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)">E2</th><td style="text-align:left">=E1*Commission</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E3</th><td style="text-align:left">=Fee</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E4</th><td style="text-align:left">=Delivery</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E5</th><td style="text-align:left">=TaxI*SUM(<font color="Blue">E2:E4</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E6</th><td style="text-align:left">=TaxII*SUM(<font color="Blue">E1:E4</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I1</th><td style="text-align:left">=(<font color="Blue">G2-(<font color="Red">TaxI+TaxII+1</font>)*(<font color="Red">Fee+Delivery</font>)</font>)/(<font color="Blue">1+Commission+Commission*TaxI+Commission*TaxII+TaxII</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">=I1*Commission</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I3</th><td style="text-align:left">=Fee</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I4</th><td style="text-align:left">=Delivery</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I5</th><td style="text-align:left">=TaxI*SUM(<font color="Blue">I2:I4</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I6</th><td style="text-align:left">=TaxII*SUM(<font color="Blue">I1:I4</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E8</th><td style="text-align:left">=SUM(<font color="Blue">E1:E6</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I8</th><td style="text-align:left">=SUM(<font color="Blue">I1:I6</font>)</td></tr></tbody></table></td></tr></table><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>Workbook Defined Names</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">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Commission</th><td style="text-align:left">=Sheet1!$B$1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Delivery</th><td style="text-align:left">=Sheet1!$B$3</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Fee</th><td style="text-align:left">=Sheet1!$B$2</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">TaxI</th><td style="text-align:left">=Sheet1!$B$4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">TaxII</th><td style="text-align:left">=Sheet1!$B$5</td></tr></tbody></table></td></tr></table><br />


Case 3:

<b></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 /><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><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Commission</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Amount</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">100.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Grand Total</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Amount</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">100.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Fee</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">1.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Commission</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">138.70</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Commission</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Delivery</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">6.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Fee</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">1.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Fee</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">1.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tax I Flat</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Delivery</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">6.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Delivery</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">6.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tax II %</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10%</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tax I</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tax I</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">10.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tax II</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">11.70</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Tax II</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">11.70</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Grand Total</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">138.70</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">Grand Total</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FFFFFF;;">138.70</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)">E2</th><td style="text-align:left">=E1*Commission</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E3</th><td style="text-align:left">=Fee</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E4</th><td style="text-align:left">=Delivery</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E5</th><td style="text-align:left">=TaxI*E1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E6</th><td style="text-align:left">=TaxII*SUM(<font color="Blue">E1:E4</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I1</th><td style="text-align:left">=(<font color="Blue">G2-(<font color="Red">TaxII+1</font>)*(<font color="Red">Fee+Delivery</font>)</font>)/(<font color="Blue">1+Commission+Commission*TaxII+TaxI+TaxII</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">=I1*Commission</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I3</th><td style="text-align:left">=Fee</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I4</th><td style="text-align:left">=Delivery</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I5</th><td style="text-align:left">=TaxI*I1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I6</th><td style="text-align:left">=TaxII*SUM(<font color="Blue">I1:I4</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E8</th><td style="text-align:left">=SUM(<font color="Blue">E1:E6</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I8</th><td style="text-align:left">=SUM(<font color="Blue">I1:I6</font>)</td></tr></tbody></table></td></tr></table><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>Workbook Defined Names</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">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Commission</th><td style="text-align:left">=Sheet1!$B$1</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Delivery</th><td style="text-align:left">=Sheet1!$B$3</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">Fee</th><td style="text-align:left">=Sheet1!$B$2</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">TaxI</th><td style="text-align:left">=Sheet1!$B$4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">TaxII</th><td style="text-align:left">=Sheet1!$B$5</td></tr></tbody></table></td></tr></table><br />


Why so many scenarios? Don't you know your fee/tax structure?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

ykamal

New Member
Joined
Jul 30, 2019
Messages
11
Case 1:

ABCDEFGHI
1Commission10%Amount100.00Grand TotalAmount100.00
2Fee1.00Commission10.00128.70Commission10.00
3Delivery6.00Fee1.00Fee1.00
4Tax I Flat10%Delivery6.00Delivery6.00
5Tax II %10%Tax I1.70Tax I1.70
6Tax II10.00Tax II10.00
7
8Grand Total128.70Grand Total128.70

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
E2=E1*Commission
E3=Fee
E4=Delivery
E5=TaxI*SUM(E2:E4)
E6=TaxII*E1
I1=(G2-(TaxI+1)*(Fee+Delivery))/(1+Commission+Commission*TaxI+TaxII)
I2=I1*Commission
I3=Fee
I4=Delivery
I5=TaxI*SUM(I2:I4)
I6=TaxII*I1
E8=SUM(E1:E6)
I8=SUM(I1:I6)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
Commission=Sheet1!$B$1
Delivery=Sheet1!$B$3
Fee=Sheet1!$B$2
TaxI=Sheet1!$B$4
TaxII=Sheet1!$B$5

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>




Case 2:

ABCDEFGHI
1Commission10%Amount100.00Grand TotalAmount100.00
2Fee1.00Commission10.00130.40Commission10.00
3Delivery6.00Fee1.00Fee1.00
4Tax I Flat10%Delivery6.00Delivery6.00
5Tax II %10%Tax I1.70Tax I1.70
6Tax II11.70Tax II11.70
7
8Grand Total130.40Grand Total130.40

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
E2=E1*Commission
E3=Fee
E4=Delivery
E5=TaxI*SUM(E2:E4)
E6=TaxII*SUM(E1:E4)
I1=(G2-(TaxI+TaxII+1)*(Fee+Delivery))/(1+Commission+Commission*TaxI+Commission*TaxII+TaxII)
I2=I1*Commission
I3=Fee
I4=Delivery
I5=TaxI*SUM(I2:I4)
I6=TaxII*SUM(I1:I4)
E8=SUM(E1:E6)
I8=SUM(I1:I6)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
Commission=Sheet1!$B$1
Delivery=Sheet1!$B$3
Fee=Sheet1!$B$2
TaxI=Sheet1!$B$4
TaxII=Sheet1!$B$5

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>




Case 3:

ABCDEFGHI
1Commission10%Amount100.00Grand TotalAmount100.00
2Fee1.00Commission10.00138.70Commission10.00
3Delivery6.00Fee1.00Fee1.00
4Tax I Flat10%Delivery6.00Delivery6.00
5Tax II %10%Tax I10.00Tax I10.00
6Tax II11.70Tax II11.70
7
8Grand Total138.70Grand Total138.70

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
E2=E1*Commission
E3=Fee
E4=Delivery
E5=TaxI*E1
E6=TaxII*SUM(E1:E4)
I1=(G2-(TaxII+1)*(Fee+Delivery))/(1+Commission+Commission*TaxII+TaxI+TaxII)
I2=I1*Commission
I3=Fee
I4=Delivery
I5=TaxI*I1
I6=TaxII*SUM(I1:I4)
E8=SUM(E1:E6)
I8=SUM(I1:I6)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers To
Commission=Sheet1!$B$1
Delivery=Sheet1!$B$3
Fee=Sheet1!$B$2
TaxI=Sheet1!$B$4
TaxII=Sheet1!$B$5

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>




Why so many scenarios? Don't you know your fee/tax structure?

Thank you soooooooooooooo much Dear Eric.
Sorry i just forget do discuss all the scenarios, When i try to look at this with different scenario then i know about its to many scenario.

Bundle of thanks I am surprise how you can do ? can you please tell me about your self ? because your mathematics are amazing.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,822
First of all, you don't need to "Reply with Quote" every time. It's easy enough to see what I wrote from my original posts. Use that button if there's a particular snippet of the message you want to refer to.

Next, the mathematics really isn't that hard! With the exception of the I1 formulas, all the other formulas I took directly from your descriptions. You should be able to see quite easily how to do those. The I1 formulas are just derived from basic algebra. This isn't a math class, so I won't go through all the details, but take a formula like your third case:

A + AC + F + D + A(T1) + (T2)(A + AC + F + D) = G.

That's pretty much directly from your description, and from here, in math terms, you just solve for A. Anyone more or less current on algebra can solve that, there are several such people on this board. I hope you got what you need! :biggrin:
 

ykamal

New Member
Joined
Jul 30, 2019
Messages
11
[FONT=&quot]Eric Thanks

You are right. But I just want to learn to solve such problem in my business. I am just a Computer Programmer I don't know much about algebra can you guide me how to learn Algebra and to resolve such problem ?
I will be very thanks full if you give me to learn this from the basic ? any link any site any tutorial ?



[/FONT]
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,822

ADVERTISEMENT

I agree, if this kind of problem crops up in your business often, you'd be wise to learn how to do them. I'm a little surprised a Computer Programmer doesn't have some algebra experience. Some of the skills translate directly from one discipline to the other. Logical, sequential thinking for example. As far as where to go, I can't help much. My training was decades ago. It also depends on your learning style. If you type "algebra tutorial" into Google, it'll come up with lots of options, paid tutors, free links, videos, and more. See if something there will work for you.
 

ykamal

New Member
Joined
Jul 30, 2019
Messages
11
[FONT=&quot]Dear Eric

I am facing another issue with the above formulas.
if i change The Commission form Percentage % to Flat with all scenario it show wrong result kindly help me in this regard.
Can i sent you all scenario aging please need your email address so I can send you Excel file.

thanks waiting for your Reply



[/FONT]
 

Watch MrExcel Video

Forum statistics

Threads
1,129,845
Messages
5,638,682
Members
417,042
Latest member
kshipp91

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