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

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

Eric W

MrExcel MVP
Welcome to the Forum.

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.

ykamal

New Member
Thanks My Dear, Can you please upload excel file so I can better understand.

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

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

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

ykamal

New Member
Dear Eric W
you are genius person I really solute you for the given Logic.

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

Eric W

MrExcel MVP

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:

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

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

ykamal

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

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

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

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

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

Eric W

MrExcel MVP

Under that scenario, only the following formulas change:

ykamal

New Member
Bundle of Thanks Eric You are my hero

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

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

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

</tbody>

<tbody>
</tbody>

ykamal

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

ykamal

New Member
Dear Eric W

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

Replies
0
Views
340
Replies
2
Views
363
Replies
3
Views
3K
Replies
2
Views
363
Replies
0
Views
396

Forum statistics

1,126,931
Messages
5,621,661
Members
415,849
Latest member
PhoenixRising2015

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.

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