reverse-percentage-calculation-on-percentage-fee

[ykamal]

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]

Welcome to the Forum.

Book1
	A	B	C	D	E	F	G	H	I
1	Commission	10%		Amount	100		Grand Total	Amount	100
2	Fee	1		Commission	10		120.20	Commission	10
3	Delivery	6		Fee	1			Fee	1
4	Tax I Flat	1.5		Delivery	6			Delivery	6
5	Tax II %	10%		Tax I Flat	1.50			Tax I Flat	1.50
6				Tax II	1.70			Tax II	1.70
7
8				Grand Total	120.20			Grand Total	120.2
Sheet3

Cell Formulas
Range	Formula
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.

 

[ykamal]

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

Welcome to the Forum.

	A	B	C	D	E	F	G	H	I
1	Commission	10%		Amount	100		Grand Total	Amount	100
2	Fee	1		Commission	10		120.20	Commission	10
3	Delivery	6		Fee	1			Fee	1
4	Tax I Flat	1.5		Delivery	6			Delivery	6
5	Tax II %	10%		Tax I Flat	1.50			Tax I Flat	1.50
6				Tax II	1.70			Tax II	1.70
7
8				Grand Total	120.20			Grand Total	120.2

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

Sheet3​

Worksheet Formulas Cell Formula 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.

 

[ykamal]

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]

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
	A	B	C	D	E	F	G	H	I
1	Commission	10%		Amount	100		Grand Total	Amount	100
2	Fee	1		Commission	10		120.20	Commission	10
3	Delivery	6		Fee	1			Fee	1
4	Tax I Flat	1.5		Delivery	6			Delivery	6
5	Tax II %	10%		Tax I Flat	1.50			Tax I Flat	1.50
6				Tax II	1.70			Tax II	1.70
7
8				Grand Total	120.20			Grand Total	120.2
Sheet3

Cell Formulas
Range	Formula
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
Name	Refers To	Cells
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

 

[ykamal]

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:

	A	B	C	D	E	F	G	H	I
1	Commission	10%		Amount	100		Grand Total	Amount	100
2	Fee	1		Commission	10		120.20	Commission	10
3	Delivery	6		Fee	1			Fee	1
4	Tax I Flat	1.5		Delivery	6			Delivery	6
5	Tax II %	10%		Tax I Flat	1.50			Tax I Flat	1.50
6				Tax II	1.70			Tax II	1.70
7
8				Grand Total	120.20			Grand Total	120.2

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

Sheet3​

Worksheet Formulas Cell Formula 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 Name Refers 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

 

[Eric W]

Under that scenario, only the following formulas change:

Book1
	A	B	C	D	E	F	G	H	I
1	Commission	10%		Amount	100		Grand Total	Amount	100
2	Fee	1		Commission	10		130.20	Commission	10
3	Delivery	6		Fee	1			Fee	1
4	Tax I Flat	1.50		Delivery	6			Delivery	6
5	Tax II %	10%		Tax I Flat	1.50			Tax I Flat	1.50
6				Tax II	11.70			Tax II	11.70
7
8				Grand Total	130.20			Grand Total	130.20
Sheet3

Cell Formulas
Range	Formula
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
Name	Refers To	Cells
Commission	=Sheet3!$B$1
Delivery	=Sheet3!$B$3
Fee	=Sheet3!$B$2
TaxI	=Sheet3!$B$4
TaxII	=Sheet3!$B$5

 

[ykamal]

Bundle of Thanks Eric You are my hero

Under that scenario, only the following formulas change:

	A	B	C	D	E	F	G	H	I
1	Commission	10%		Amount	100		Grand Total	Amount	100
2	Fee	1		Commission	10		130.20	Commission	10
3	Delivery	6		Fee	1			Fee	1
4	Tax I Flat	1.50		Delivery	6			Delivery	6
5	Tax II %	10%		Tax I Flat	1.50			Tax I Flat	1.50
6				Tax II	11.70			Tax II	11.70
7
8				Grand Total	130.20			Grand Total	130.20

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

Sheet3​

Worksheet Formulas Cell Formula 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 Name Refers 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>

 

[ykamal]

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]

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