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,

[TABLE="width: 281"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]75.000%[/TD]
[TD="align: right"]2.00%[/TD]
[/TR]
[TR]
[TD="align: right"]75.00%[/TD]
[TD="align: right"]90.000%[/TD]
[TD="align: right"]3.00%[/TD]
[/TR]
[TR]
[TD="align: right"]90.00%[/TD]
[TD="align: right"]100.000%[/TD]
[TD="align: right"]4.00%[/TD]
[/TR]
[TR]
[TD="align: right"]100.00%[/TD]
[TD="align: right"]110.000%[/TD]
[TD="align: right"]5.00%[/TD]
[/TR]
[TR]
[TD="align: right"]110.00%[/TD]
[TD][/TD]
[TD="align: right"]6.00%[/TD]
[/TR]
</tbody>[/TABLE]
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]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

0.00%​

[/td][td]

0​

[/td][td][/td][td]

100.00%​

[/td][td]

3000000​

[/td][td]

80000​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

75.00%​

[/td][td]

2.00%​

[/td][td]

45000​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

90.00%​

[/td][td]

3.00%​

[/td][td]

13500​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

100.00%​

[/td][td]

4.00%​

[/td][td]

12000​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

110.00%​

[/td][td]

5.00%​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

999%​

[/td][td]

6.00%​

[/td][td]

0​

[/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td]

70500​

[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet30[/td][/tr][/table]

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?