Sliding scale commission percentages formulas
Hi,
You need a table very similar to the one you posted below looking like this:-
Col A Col B
0 1.75
31 3.5
61 12.5
81 17.5
101 21.7
105 23.45
109 25.2
113 25.9
117 27.3
As you will see I haven't replicated all your data but you get the idea.
The lets say you are trying to the appropriate rate for a value in D1 you
would use this formula:-
=VLOOKUP(D1,A1:B9,2)
Mike
"Margs" wrote:
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%
|