Calculating Tier %'s for Commission Calculation When you have a total payout

[KimberlyWelty]

So, I am trying to determine how best to calculate Tie rates for a commission structure when I know what the total payout should be at 100% achieved? Here is a sample of the tier structures,

0.00%	75.000%	2.00%
75.00%	90.000%	3.00%
90.00%	100.000%	4.00%
100.00%	110.000%	5.00%
110.00%		6.00%

<colgroup><col><col><col></colgroup><tbody>
</tbody>

The problem I am having is that if a person's quota is $3,000,000 and there total compensation needs to be $80,000 if they achieve 100%, how can I have it calculate the proper % earned in each category. I am sure there has to be a formula to do this. Any assistance would be greatly appreciated.

 

[Oscar Cronquist]

	A​	B​	C​	D​	E​	F​
1​	0.00%​	0​		100.00%​	3000000​	80000​
2​	75.00%​	2.00%​	45000​
3​	90.00%​	3.00%​	13500​
4​	100.00%​	4.00%​	12000​
5​	110.00%​	5.00%​	0​
6​	999%​	6.00%​	0​
7​			70500​

Sheet: Sheet30

Array formula in cell C2:C6:
=(A2:A6<=D1)*(A2:A6-A1:A5)*E1*B2:B6

Formula in cell C7:
=SUMPRODUCT((A2:A6<=D1)*(A2:A6-A1:A5)*E1*B2:B6)

 

[KimberlyWelty]

Oscar - thanks for responding. So I don't understand how it will tell me what %'s need to be used in each category to achieve the $80,000 payout. I want it to tell me what should be in cells B2,B3, and B4 as a percent to ensure that if they hit the 100% at $3,000,000 they will receive the proper payouts at the correct % to achieve the $80,000. Right now, it is calculating to $70,500, how do I get it to calculate the % for me?