Lookup Question - paying % of sales commission based on % to goal

[caper12]

Hi, was hoping someone might be able to help me come up with a formula for sales % to goal payout....for example, if they reach:
<=80% pay 2.5% of actual sales
81-89% pay 3%
90-94% pay 3.5%
95-99 pay 4%
>=100 pay 5%

I'm not sure how I would set up a lookup table since it's a range and having trouble with the formula.....any help would be greatly appreciated. Thanks so much

 

[steve the fish]

In A1:B6:

<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>
[TD="width: 64"]Sales %[/TD]
[TD="width: 64"]Payout %[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0.025[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"]0.03[/TD]

[TD="align: right"]90[/TD]
[TD="align: right"]0.035[/TD]

[TD="align: right"]95[/TD]
[TD="align: right"]0.04[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]0.05
[/TD]

</tbody>

In D1 place your sales then:

=LOOKUP(D1,A2:A6,B2:B6)
[TABLE="width: 128"]
<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[/TR]
[TR]

[/TR]
[TR]

[/TR]
[TR]

[/TR]
[TR]

[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[arthurbr]

http://www.mcgimpsey.com/excel/variablerate1.html

 

[caper12]

in a1:b6:

sales %

<tbody>
[td="width: 64"]payout %[/td]

[td="align: Right"]0[/td]
[td="align: Right"]0.025[/td]

[td="align: Right"]80[/td]
[td="align: Right"]0.03[/td]

[td="align: Right"]90[/td]
[td="align: Right"]0.035[/td]

[td="align: Right"]95[/td]
[td="align: Right"]0.04[/td]

[td="align: Right"]100[/td]
[td="align: Right"]0.05[/td]

</tbody>

in d1 place your sales then:

=lookup(d1,a2:a6,b2:b6)
[table="width: 128"]
<tbody>[tr]
[td="width: 64"][/td]
[/tr]
[tr]
[/tr]
[tr]
[/tr]
[tr]
[/tr]
[tr]
[/tr]
[tr]
[td="align: Right"][/td]
[/tr]
</tbody>[/table]

thank you

 

[caper12]

Thanks so much!