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

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)
Welcome to the Forum.


Book1
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
Sheet3
Cell Formulas
RangeFormula
E2=E1*$B$1
E3=$B$2
E4=$B$3
E5=$B$4
E6=SUM(E2:E4)*$B$5
E8=SUM(E1:E6)
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
I8=SUM(I1:I6)


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.
 
Upvote 0
Thanks My Dear, Can you please upload excel file so I can better understand.

Welcome to the Forum.

ABCDEFGHI
CommissionAmountGrand TotalAmount
FeeCommissionCommission
DeliveryFeeFee
Tax I FlatDeliveryDelivery
Tax II %Tax I FlatTax I Flat
Tax IITax II
Grand TotalGrand Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]10%[/TD]
[TD="align: right"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: right"]100[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]120.20[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1.5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10%[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1.50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1.50[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1.70[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1.70[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]120.20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]120.2[/TD]

</tbody>
Sheet3

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=E1*$B$1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=$B$2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4[/TH]
[TD="align: left"]=$B$3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E5[/TH]
[TD="align: left"]=$B$4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E6[/TH]
[TD="align: left"]=SUM(E2:E4)*$B$5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I1[/TH]
[TD="align: left"]=(G2-$B$2-$B$3-$B$4-($B$2+$B$3)*$B$5)/(1+$B$1+$B$1*$B$5)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=I1*$B$1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I3[/TH]
[TD="align: left"]=$B$2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I4[/TH]
[TD="align: left"]=$B$3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I5[/TH]
[TD="align: left"]=$B$4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I6[/TH]
[TD="align: left"]=SUM(I2:I4)*$B$5[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E8[/TH]
[TD="align: left"]=SUM(E1:E6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I8[/TH]
[TD="align: left"]=SUM(I1:I6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.
 
Upvote 0
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
 
Upvote 0
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:


Book1
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
Sheet3
Cell Formulas
RangeFormula
E2=E1*Commission
E3=Fee
E4=Delivery
E5=TaxI
E6=SUM(E2:E4)*TaxII
E8=SUM(E1:E6)
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
I8=SUM(I1:I6)
Named Ranges
NameRefers ToCells
Commission=Sheet3!$B$1
Delivery=Sheet3!$B$3
Fee=Sheet3!$B$2
TaxI=Sheet3!$B$4
TaxII=Sheet3!$B$5


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

https://support.office.com/en-us/ar...formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64
 
Upvote 0
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




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
CommissionAmountGrand TotalAmount
FeeCommissionCommission
DeliveryFeeFee
Tax I FlatDeliveryDelivery
Tax II %Tax I FlatTax I Flat
Tax IITax II
Grand TotalGrand Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]10%[/TD]
[TD="align: right"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: right"]100[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]120.20[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1.5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10%[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1.50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1.50[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1.70[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1.70[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]120.20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]120.2[/TD]

</tbody>
Sheet3

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=E1*Commission[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]=Fee[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4[/TH]
[TD="align: left"]=Delivery[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E5[/TH]
[TD="align: left"]=TaxI[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E6[/TH]
[TD="align: left"]=SUM(E2:E4)*TaxII[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I1[/TH]
[TD="align: left"]=(G2-Fee-Delivery-TaxI-(Fee+Delivery)*TaxII)/(1+Commission+Commission*TaxII)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]=I1*Commission[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I3[/TH]
[TD="align: left"]=Fee[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I4[/TH]
[TD="align: left"]=Delivery[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I5[/TH]
[TD="align: left"]=TaxI[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I6[/TH]
[TD="align: left"]=SUM(I2:I4)*TaxII[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E8[/TH]
[TD="align: left"]=SUM(E1:E6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I8[/TH]
[TD="align: left"]=SUM(I1:I6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Commission[/TH]
[TD="align: left"]=Sheet3!$B$1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Delivery[/TH]
[TD="align: left"]=Sheet3!$B$3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Fee[/TH]
[TD="align: left"]=Sheet3!$B$2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]TaxI[/TH]
[TD="align: left"]=Sheet3!$B$4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]TaxII[/TH]
[TD="align: left"]=Sheet3!$B$5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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

https://support.office.com/en-us/ar...formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64
 
Upvote 0
Under that scenario, only the following formulas change:


Book1
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
Sheet3
Cell Formulas
RangeFormula
E6=SUM(E1:E4)*TaxII
I6=SUM(I1:I4)*TaxII
I1=(G2-Fee-Delivery-TaxI-(Fee+Delivery)*TaxII)/(1+Commission+(1+Commission)*TaxII)
Named Ranges
NameRefers ToCells
Commission=Sheet3!$B$1
Delivery=Sheet3!$B$3
Fee=Sheet3!$B$2
TaxI=Sheet3!$B$4
TaxII=Sheet3!$B$5
 
Upvote 0
Bundle of Thanks Eric You are my hero

Under that scenario, only the following formulas change:

ABCDEFGHI
CommissionAmountGrand TotalAmount
FeeCommissionCommission
DeliveryFeeFee
Tax I FlatDeliveryDelivery
Tax II %Tax I FlatTax I Flat
Tax IITax II
Grand TotalGrand Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]10%[/TD]
[TD="align: right"][/TD]

[TD="align: right"]100[/TD]
[TD="align: right"][/TD]

[TD="align: right"]100[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]130.20[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1.50[/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]10%[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1.50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1.50[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]11.70[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]11.70[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]130.20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]130.20[/TD]

</tbody>
Sheet3

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E6[/TH]
[TD="align: left"]=SUM(E1:E4)*TaxII[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I6[/TH]
[TD="align: left"]=SUM(I1:I4)*TaxII[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I1[/TH]
[TD="align: left"]=(G2-Fee-Delivery-TaxI-(Fee+Delivery)*TaxII)/(1+Commission+(1+Commission)*TaxII)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Commission[/TH]
[TD="align: left"]=Sheet3!$B$1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Delivery[/TH]
[TD="align: left"]=Sheet3!$B$3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Fee[/TH]
[TD="align: left"]=Sheet3!$B$2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]TaxI[/TH]
[TD="align: left"]=Sheet3!$B$4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]TaxII[/TH]
[TD="align: left"]=Sheet3!$B$5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

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
Back
Top