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]

Case 1:

Book1
	A	B	C	D	E	F	G	H	I
1	Commission	10%		Amount	100.00		Grand Total	Amount	100.00
2	Fee	1.00		Commission	10.00		128.70	Commission	10.00
3	Delivery	6.00		Fee	1.00			Fee	1.00
4	Tax I Flat	10%		Delivery	6.00			Delivery	6.00
5	Tax II %	10%		Tax I	1.70			Tax I	1.70
6				Tax II	10.00			Tax II	10.00
7
8				Grand Total	128.70			Grand Total	128.70
Sheet1

Cell Formulas
Range	Formula
E2	=E1*Commission
E3	=Fee
E4	=Delivery
E5	=TaxI*SUM(E2:E4)
E6	=TaxII*E1
E8	=SUM(E1:E6)
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
I8	=SUM(I1:I6)

Named Ranges
Name	Refers To	Cells
Commission	=Sheet1!$B$1
Delivery	=Sheet1!$B$3
Fee	=Sheet1!$B$2
TaxI	=Sheet1!$B$4
TaxII	=Sheet1!$B$5

Case 2:

Book1
	A	B	C	D	E	F	G	H	I
1	Commission	10%		Amount	100.00		Grand Total	Amount	100.00
2	Fee	1.00		Commission	10.00		130.40	Commission	10.00
3	Delivery	6.00		Fee	1.00			Fee	1.00
4	Tax I Flat	10%		Delivery	6.00			Delivery	6.00
5	Tax II %	10%		Tax I	1.70			Tax I	1.70
6				Tax II	11.70			Tax II	11.70
7
8				Grand Total	130.40			Grand Total	130.40
Sheet1

Cell Formulas
Range	Formula
E2	=E1*Commission
E3	=Fee
E4	=Delivery
E5	=TaxI*SUM(E2:E4)
E6	=TaxII*SUM(E1:E4)
E8	=SUM(E1:E6)
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)
I8	=SUM(I1:I6)

Named Ranges
Name	Refers To	Cells
Commission	=Sheet1!$B$1
Delivery	=Sheet1!$B$3
Fee	=Sheet1!$B$2
TaxI	=Sheet1!$B$4
TaxII	=Sheet1!$B$5

Case 3:

Book1
	A	B	C	D	E	F	G	H	I
1	Commission	10%		Amount	100.00		Grand Total	Amount	100.00
2	Fee	1.00		Commission	10.00		138.70	Commission	10.00
3	Delivery	6.00		Fee	1.00			Fee	1.00
4	Tax I Flat	10%		Delivery	6.00			Delivery	6.00
5	Tax II %	10%		Tax I	10.00			Tax I	10.00
6				Tax II	11.70			Tax II	11.70
7
8				Grand Total	138.70			Grand Total	138.70
Sheet1

Cell Formulas
Range	Formula
E2	=E1*Commission
E3	=Fee
E4	=Delivery
E5	=TaxI*E1
E6	=TaxII*SUM(E1:E4)
E8	=SUM(E1:E6)
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)
I8	=SUM(I1:I6)

Named Ranges
Name	Refers To	Cells
Commission	=Sheet1!$B$1
Delivery	=Sheet1!$B$3
Fee	=Sheet1!$B$2
TaxI	=Sheet1!$B$4
TaxII	=Sheet1!$B$5

Why so many scenarios? Don't you know your fee/tax structure?

 

[ykamal]

Case 1:

	A	B	C	D	E	F	G	H	I
1	Commission	10%		Amount	100.00		Grand Total	Amount	100.00
2	Fee	1.00		Commission	10.00		128.70	Commission	10.00
3	Delivery	6.00		Fee	1.00			Fee	1.00
4	Tax I Flat	10%		Delivery	6.00			Delivery	6.00
5	Tax II %	10%		Tax I	1.70			Tax I	1.70
6				Tax II	10.00			Tax II	10.00
7
8				Grand Total	128.70			Grand Total	128.70

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

Sheet1​

Worksheet Formulas Cell Formula 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) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To Commission =Sheet1!$B$1 Delivery =Sheet1!$B$3 Fee =Sheet1!$B$2 TaxI =Sheet1!$B$4 TaxII =Sheet1!$B$5 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Case 2:

	A	B	C	D	E	F	G	H	I
1	Commission	10%		Amount	100.00		Grand Total	Amount	100.00
2	Fee	1.00		Commission	10.00		130.40	Commission	10.00
3	Delivery	6.00		Fee	1.00			Fee	1.00
4	Tax I Flat	10%		Delivery	6.00			Delivery	6.00
5	Tax II %	10%		Tax I	1.70			Tax I	1.70
6				Tax II	11.70			Tax II	11.70
7
8				Grand Total	130.40			Grand Total	130.40

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

Sheet1​

Worksheet Formulas Cell Formula 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) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To Commission =Sheet1!$B$1 Delivery =Sheet1!$B$3 Fee =Sheet1!$B$2 TaxI =Sheet1!$B$4 TaxII =Sheet1!$B$5 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Case 3:

	A	B	C	D	E	F	G	H	I
1	Commission	10%		Amount	100.00		Grand Total	Amount	100.00
2	Fee	1.00		Commission	10.00		138.70	Commission	10.00
3	Delivery	6.00		Fee	1.00			Fee	1.00
4	Tax I Flat	10%		Delivery	6.00			Delivery	6.00
5	Tax II %	10%		Tax I	10.00			Tax I	10.00
6				Tax II	11.70			Tax II	11.70
7
8				Grand Total	138.70			Grand Total	138.70

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

Sheet1​

Worksheet Formulas Cell Formula 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) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To Commission =Sheet1!$B$1 Delivery =Sheet1!$B$3 Fee =Sheet1!$B$2 TaxI =Sheet1!$B$4 TaxII =Sheet1!$B$5 <thead> </thead><tbody> </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]

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]

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

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]

Thanks Eric I got your point.
Again thanks for your help....

 

[ykamal]

[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.
Can i sent you all scenario aging please need your email address so I can send you Excel file.

thanks waiting for your Reply



[/FONT]