Sliding scale commission percentages formulas
Hi Margs,
With your sales achieved in A1 put this in B1.
=LOOKUP(A1,{0,31,61,81,101,105,109,113,117,121,125 ,131,141,151,161,171},{1.75,3.5,12.5,17.5,21.7,23. 45,25.2,25.9,27.3,29.75,32.2,36.4,37.8,42,46.2,51. 1})
Watch out for the line wrap if you copy from this post.
HTH
Martin
"Margs" wrote in message
...
How would I make this table apply to each cell that contains my different
sales % figures in order to see what my Rate Earned would be? I am a
total
novice. Thank you in advance.
Sales % Achieved Rate Earned
0 but < or = 30% 1.75%
30 but < or = 60% 3.50%
60 but< or = 80% 12.50%
80 but < or =100% 17.50%
100 but < or =104% 21.70%
104 but < or =108% 23.45%
108 but < or =112% 25.20%
112 but < or =116% 25.90%
116 but < or =120% 27.30%
120 but < or =124% 29.75%
124 but < or =130% 32.20%
130 but < or =140% 36.40%
140 but < or =150% 37.80%
150 but < or =160% 42.00%
160 but < or =170% 46.20%
170% 51.10%
new business 84.00%
|