Commission Calculation

**ApplianceGuy15** · 01-17-2007, 04:15 PM

I am struggiling to come up with a commission formula that uses our varying commisson schedule. The formula needs to apply a commision rate based on a gross profit percentage. Our schedule is below. I need a formula that includes all these rules. I tried making it with if/than statments, but failed miserably. Can this be done? Any help would be much appreciated.

Gross Profit % Commission Rate
25% and > 0.2
24 0.193
23 0.186
22 0.18
21 0.172
20 0.166
19 0.16
18 0.152
17 0.146
16 0.14
15 0.133
14 0.126
13 0.12
12 0.113
11 0.107
<=10 0.1

**VBA Noob** · 01-17-2007, 04:23 PM

Hi,

Does this help

=LOOKUP(A1,{0,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25},{0.1,0.107,0.113,0.12,0.126,0.133,0.14,0.146,0.152,0.16,0.166,0.172,0.18,0.186,0.193,0.2})

VBA Noob