Sliding scale commission percentages formulas

[Margs]

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%

 

[Mike H]

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

 

[MartinW]

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

 

[Teethless mama]

=IF(A1="new
business",0.84,IF(ISNUMBER(A1),LOOKUP(A1,{0,31,61,81,101,105,109,113,117,121,124,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}/100),""))

 

[Teethless mama]

Correction:

=IF(A1="new
business",0.84,IF(ISNUMBER(A1),LOOKUP(A1,{0,1,31,61,81,101,105,109,113,117,121,124,131,141,151,161,171},{0,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}/100),""))

:

 

[MartinW]

Hi Teethless mama,

Why would you divide by 100?

Format the cell as custom 0.00"%"

Regards
Martin

 

[Teethless mama]

Divide by 100 will give the right result when OP format as percentage

eg. 1.75/100 format as percentage the result 1.75%
if you take 1.75 format as percentage the result 175.00%

Not everyone knows how to do custom format 0.00"%" without providing the
instruction.

Thanks for your concern.

 

[MartinW]

Ah, now I see what you were doing. I never use the percentage format
in Excel, or the percentage key on a calculator for that matter.

Regards
Martin