# reverse-percentage-calculation-on-percentage-fee

#### ykamal

##### New Member
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
Case 1:

Case 2:

Case 3:

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

</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)

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

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

</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)

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

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

</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)

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

</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
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!

#### ykamal

##### New Member
[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

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
Thanks Eric I got your point.

#### ykamal

##### New Member
[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.

[/FONT]

Replies
0
Views
361
Replies
2
Views
371
Replies
3
Views
3K
Replies
2
Views
376
Replies
0
Views
415

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.

### Which adblocker are you using?

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

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