reverse-percentage-calculation-on-percentage-fee

Y

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 ??
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Eric W

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,802
Welcome to the Forum.

<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=";">Commission</td><td style="text-align: right;;">10%</td><td style="text-align: right;;"></td><td style=";">Amount</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style=";">Grand Total</td><td style=";">Amount</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Fee</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">Commission</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">120.20</td><td style=";">Commission</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Delivery</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style=";">Fee</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Fee</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Tax I Flat</td><td style="text-align: right;;">1.5</td><td style="text-align: right;;"></td><td style=";">Delivery</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Delivery</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Tax II %</td><td style="text-align: right;;">10%</td><td style="text-align: right;;"></td><td style=";">Tax I Flat</td><td style="text-align: right;;">1.50</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Tax I Flat</td><td style="text-align: right;;">1.50</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Tax II</td><td style="text-align: right;;">1.70</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Tax II</td><td style="text-align: right;;">1.70</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</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: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Grand Total</td><td style="text-align: right;;">120.20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Grand Total</td><td style="text-align: right;;">120.2</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)">Sheet3</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*$B$1</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">=$B$2</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">=$B$3</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">=$B$4</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">=SUM(<font color="Blue">E2:E4</font>)*$B$5</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-$B$2-$B$3-$B$4-(<font color="Red">$B$2+$B$3</font>)*$B$5</font>)/(<font color="Blue">1+$B$1+$B$1*$B$5</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*$B$1</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">=$B$2</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">=$B$3</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">=$B$4</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">=SUM(<font color="Blue">I2:I4</font>)*$B$5</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 />

The only real tricky one is the I1 formula. Enter your constants in B1:B5. Now enter an Amount in E1, and the various parts are calculated. Or enter the Grand Total in G2, and the various part are calculated again. The only real tricky formula is I1.
 
Y

ykamal

New Member
Joined
Jul 30, 2019
Messages
11
Thanks My Dear, Can you please upload excel file so I can better understand.

Eric W said:
Welcome to the Forum.

ABCDEFGHI
1Commission10%Amount100Grand TotalAmount100
2Fee1Commission10120.20Commission10
3Delivery6Fee1Fee1
4Tax I Flat1.5Delivery6Delivery6
5Tax II %10%Tax I Flat1.50Tax I Flat1.50
6Tax II1.70Tax II1.70
7
8Grand Total120.20Grand Total120.2

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

Worksheet Formulas
CellFormula
E2=E1*$B$1
E3=$B$2
E4=$B$3
E5=$B$4
E6=SUM(E2:E4)*$B$5
I1=(G2-$B$2-$B$3-$B$4-($B$2+$B$3)*$B$5)/(1+$B$1+$B$1*$B$5)
I2=I1*$B$1
I3=$B$2
I4=$B$3
I5=$B$4
I6=SUM(I2:I4)*$B$5
E8=SUM(E1:E6)
I8=SUM(I1:I6)

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

<tbody>
</tbody>



The only real tricky one is the I1 formula. Enter your constants in B1:B5. Now enter an Amount in E1, and the various parts are calculated. Or enter the Grand Total in G2, and the various part are calculated again. The only real tricky formula is I1.
Click to expand...
 
Y

ykamal

New Member
Joined
Jul 30, 2019
Messages
11
Dear Eric W
you are genius person I really solute you for the given Logic. :) I need your Email address ??

I have on more scenario please review this as well. only one thing is changed.
Tax II is based on Send Total

I need to Calculate Reverse Amount From Grand Total Amount.

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; line-height: inherit; color: rgb(51, 51, 51); font-variant: inherit; white-space: inherit; background-color: rgb(250, 250, 250); border: 0px; font-stretch: inherit; vertical-align: baseline; box-sizing: 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 Grand Total ( Amount + Fee + Commission + Delivery ) = 11.70
------------------------------------------------------------------
Grand Total = 130.20
------------------------------------------------------------------
</code>Now I just want its Reverse Formula To Get Amount 100 From Grand Total 130.20. I Have


waiting for your reply
 
Eric W

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,802

ADVERTISEMENT

Sorry, I'm unable to upload files. However, it should be easy to create your own file from the example I posted. Just select the table from my post, copy it, then paste it into a blank workbook. Make sure that you delete any extra rows or columns (Commission should be in the A1 cell). Or you can manually type in the headings in columns A, D, I, and cell G1, there aren't that many. Then enter the formulas from the formula table in the indicated cells.

This actually might be a good time to mention that using Named Ranges can make your formulas easier to read. If I used Named Ranges for B1:B5, then the formulas look like:

<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=";">Commission</td><td style="text-align: right;;">10%</td><td style="text-align: right;;"></td><td style=";">Amount</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style=";">Grand Total</td><td style=";">Amount</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Fee</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">Commission</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">120.20</td><td style=";">Commission</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Delivery</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style=";">Fee</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Fee</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Tax I Flat</td><td style="text-align: right;;">1.5</td><td style="text-align: right;;"></td><td style=";">Delivery</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Delivery</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Tax II %</td><td style="text-align: right;;">10%</td><td style="text-align: right;;"></td><td style=";">Tax I Flat</td><td style="text-align: right;;">1.50</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Tax I Flat</td><td style="text-align: right;;">1.50</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Tax II</td><td style="text-align: right;;">1.70</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Tax II</td><td style="text-align: right;;">1.70</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</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: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Grand Total</td><td style="text-align: right;;">120.20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Grand Total</td><td style="text-align: right;;">120.2</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)">Sheet3</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</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">=SUM(<font color="Blue">E2:E4</font>)*TaxII</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-Fee-Delivery-TaxI-(<font color="Red">Fee+Delivery</font>)*TaxII</font>)/(<font color="Blue">1+Commission+Commission*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</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">=SUM(<font color="Blue">I2:I4</font>)*TaxII</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">=Sheet3!$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">=Sheet3!$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">=Sheet3!$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">=Sheet3!$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">=Sheet3!$B$5</td></tr></tbody></table></td></tr></table><br />

Here's an explanation of how to create Named Ranges:

https://support.office.com/en-us/ar...formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64
 
Y

ykamal

New Member
Joined
Jul 30, 2019
Messages
11
Thanks Eric

I have on more scenario please review this as well. only one thing is changed.
Tax II is based on Send Total

I need to Calculate Reverse Amount From Grand Total Amount.

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; line-height: inherit; color: rgb(51, 51, 51); font-variant: inherit; white-space: inherit; background-color: rgb(250, 250, 250); border: 0px; font-stretch: inherit; vertical-align: baseline; box-sizing: 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 Grand Total ( Amount + Fee + Commission + Delivery ) = 11.70
------------------------------------------------------------------
Grand Total = 130.20
------------------------------------------------------------------
</code>Now I just want its Reverse Formula To Get Amount 100 From Grand Total 130.20. I Have


waiting for your reply



Eric W said:
Sorry, I'm unable to upload files. However, it should be easy to create your own file from the example I posted. Just select the table from my post, copy it, then paste it into a blank workbook. Make sure that you delete any extra rows or columns (Commission should be in the A1 cell). Or you can manually type in the headings in columns A, D, I, and cell G1, there aren't that many. Then enter the formulas from the formula table in the indicated cells.

This actually might be a good time to mention that using Named Ranges can make your formulas easier to read. If I used Named Ranges for B1:B5, then the formulas look like:

ABCDEFGHI
1Commission10%Amount100Grand TotalAmount100
2Fee1Commission10120.20Commission10
3Delivery6Fee1Fee1
4Tax I Flat1.5Delivery6Delivery6
5Tax II %10%Tax I Flat1.50Tax I Flat1.50
6Tax II1.70Tax II1.70
7
8Grand Total120.20Grand Total120.2

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

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

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

<tbody>
</tbody>

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

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

<tbody>
</tbody>



Here's an explanation of how to create Named Ranges:

https://support.office.com/en-us/ar...formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64
Click to expand...
 
Eric W

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,802

ADVERTISEMENT

Under that scenario, only the following formulas change:

<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=";">Commission</td><td style="text-align: right;;">10%</td><td style="text-align: right;;"></td><td style=";">Amount</td><td style="text-align: right;;">100</td><td style="text-align: right;;"></td><td style=";">Grand Total</td><td style=";">Amount</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Fee</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style=";">Commission</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">130.20</td><td style=";">Commission</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Delivery</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style=";">Fee</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Fee</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Tax I Flat</td><td style="text-align: right;;">1.50</td><td style="text-align: right;;"></td><td style=";">Delivery</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Delivery</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Tax II %</td><td style="text-align: right;;">10%</td><td style="text-align: right;;"></td><td style=";">Tax I Flat</td><td style="text-align: right;;">1.50</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Tax I Flat</td><td style="text-align: right;;">1.50</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Tax II</td><td style="text-align: right;;">11.70</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Tax II</td><td style="text-align: right;;">11.70</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</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: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Grand Total</td><td style="text-align: right;;">130.20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Grand Total</td><td style="text-align: right;;">130.20</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)">Sheet3</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)">E6</th><td style="text-align:left">=SUM(<font color="Blue">E1:E4</font>)*TaxII</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">=SUM(<font color="Blue">I1:I4</font>)*TaxII</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-Fee-Delivery-TaxI-(<font color="Red">Fee+Delivery</font>)*TaxII</font>)/(<font color="Blue">1+Commission+(<font color="Red">1+Commission</font>)*TaxII</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">=Sheet3!$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">=Sheet3!$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">=Sheet3!$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">=Sheet3!$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">=Sheet3!$B$5</td></tr></tbody></table></td></tr></table><br />
 
Y

ykamal

New Member
Joined
Jul 30, 2019
Messages
11
Bundle of Thanks Eric You are my hero

Eric W said:
Under that scenario, only the following formulas change:

ABCDEFGHI
1Commission10%Amount100Grand TotalAmount100
2Fee1Commission10130.20Commission10
3Delivery6Fee1Fee1
4Tax I Flat1.50Delivery6Delivery6
5Tax II %10%Tax I Flat1.50Tax I Flat1.50
6Tax II11.70Tax II11.70
7
8Grand Total130.20Grand Total130.20

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

Worksheet Formulas
CellFormula
E6=SUM(E1:E4)*TaxII
I6=SUM(I1:I4)*TaxII
I1=(G2-Fee-Delivery-TaxI-(Fee+Delivery)*TaxII)/(1+Commission+(1+Commission)*TaxII)

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

<tbody>
</tbody>

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

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

<tbody>
</tbody>
Click to expand...
 
Y

ykamal

New Member
Joined
Jul 30, 2019
Messages
11
Dear Eric W
I am very thanks full to you for your time I need more help.

I have on more scenario please review this as well. I need to Calculate Reverse Amount From Grand Total Amount.

1.

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; line-height: inherit; color: rgb(51, 51, 51); font-variant: inherit; white-space: inherit; background-color: rgb(250, 250, 250); border: 0px; font-stretch: inherit; vertical-align: baseline; box-sizing: inherit;">I have Amount = 100
10 % Commission on Amount = 10
Fee On Amount = 1
Delivery Charges = 6
Tax I 10 % On Fee (Commission + Free + Delivery Charges) = 1.70
Tax II 10 % On Amount = 10
------------------------------------------------------------------
Grand Total = 128.70
------------------------------------------------------------------
</code>Now I just want its Reverse Formula To Get Amount 100 From Grand Total 141.20.


2.

<code style="font-style: inherit; font-variant: inherit; font-weight: inherit; white-space: inherit; margin: 0px; padding: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; line-height: inherit; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250); border: 0px; font-stretch: inherit; vertical-align: baseline; box-sizing: inherit;">I have Amount = 100
10 % Commission on Amount = 10
Fee On Amount = 1
Delivery Charges = 6
Tax I 10 % On Fee (Commission + Free + Delivery Charges) = 1.70
Tax II 10 % On Grand Total ( Amount + Fee + Commission + Delivery ) = 11.70
------------------------------------------------------------------
Grand Total = 130.40
------------------------------------------------------------------
</code>Now I just want its Reverse Formula To Get Amount 100 From Grand Total 141.20.
 
Y

ykamal

New Member
Joined
Jul 30, 2019
Messages
11
Dear Eric W
Please ignore my previous #9 Thread

I have on more scenario please review this as well. I need to Calculate Reverse Amount From Grand Total Amount.

1.

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; line-height: inherit; color: rgb(51, 51, 51); font-variant: inherit; white-space: inherit; background-color: rgb(250, 250, 250); border: 0px; font-stretch: inherit; vertical-align: baseline; box-sizing: inherit;">I have Amount = 100
10 % Commission on Amount = 10
Fee On Amount = 1
Delivery Charges = 6
Tax I 10 % On Fee (Commission + Free + Delivery Charges) = 1.70
Tax II 10 % On Amount = 10
------------------------------------------------------------------
Grand Total = 128.70
------------------------------------------------------------------
</code>Now I just want its Reverse Formula To Get Amount 100 From Grand Total 128.70.


2.

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; line-height: inherit; color: rgb(51, 51, 51); font-variant: inherit; white-space: inherit; background-color: rgb(250, 250, 250); border: 0px; font-stretch: inherit; vertical-align: baseline; box-sizing: inherit;">I have Amount = 100
10 % Commission on Amount = 10
Fee On Amount = 1
Delivery Charges = 6
Tax I 10 % On Fee (Commission + Free + Delivery Charges) = 1.70
Tax II 10 % On Grand Total ( Amount + Fee + Commission + Delivery ) = 11.70
------------------------------------------------------------------
Grand Total = 130.40
------------------------------------------------------------------
</code>Now I just want its Reverse Formula To Get Amount 100 From Grand Total 130.40.
3.

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; line-height: inherit; color: rgb(51, 51, 51); font-variant: inherit; white-space: inherit; background-color: rgb(250, 250, 250); border: 0px; font-stretch: inherit; vertical-align: baseline; box-sizing: inherit;">I have Amount = 100
10 % Commission on Amount = 10
Fee On Amount = 1
Delivery Charges = 6
Tax I 10 % On Amount = 10
Tax II 10 % On Grand Total ( Amount + Fee + Commission + Delivery ) = 11.70
------------------------------------------------------------------
Grand Total = 138.70
------------------------------------------------------------------
</code>Now I just want its Reverse Formula To Get Amount 100 From Grand Total 138.70
 
You must log in or register to reply here.

Similar threads

rediffusion
  • Question
How can i add button to `ContextMenuShape`?
Replies
0
Views
359
rediffusion
rediffusion
A
  • Question
Conditional formatting is not working for less than TODAY(), only for greater than
Replies
2
Views
370
alexpazolini
A
J
  • Question
Extract href link from source code using VBA
Replies
3
Views
3K
Domenic
D
H
  • Question
Compare two week numbers from different years in Excel
Replies
2
Views
375
Tetra201
Tetra201
A
  • Question
At a same time refresh all data connections in all open workbooks
Replies
0
Views
415
adpatel
A

Watch MrExcel Video

Learn Excel - Slicer For Two Data Sets - Podcast 2198

Forum statistics

Threads
1,129,331
Messages
5,635,655
Members
416,870
Latest member
rikimon2

Share this page

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