calculating a commission pay % based on a % of sales in a range of percentages

[quick2learn]

Hi it's been years since I last posted and could not remember my login but do remember all the helpful people here at mrexcel. I have to pay a salesman a commission based on a percentage of their goal. I can do it just looking at the number and calculating if it is 75 or 100% of the goal but I need the HR dept to have a simple formula so the number just shows up. I need help writing the formula

Lets say I set the goal at 5000 in cell A1

Monthly sales are reported in A2
I would like to see his commissions in B2 using this scale ( If my sales A2 are at <75% of the goal A1 I get 10% commission of A2 if my sales A2 are 75-99% of A1 I get 12% if sales are 100-125% i get 15% and finally if I am >125 I get 20%. Seems simple enough. I was thinking of using a vlookup because the percentages both ranges and commission pay may change but I think a formula would work fine and I could create a lookup later if I find this is changing too much. Any help creating this formula would be much appreciated. I searched the forum first but could not find something that seemed to work. Thank you

 

[Aladin Akyurek]

In B2 enter:

=A1*LOOKUP(A2/A1,{0;0.75;1;1.26},{0.1;0.12;0.15;0.2})

 

[quick2learn]

Thank you very much this is exactly what I was looking for.

 

[quick2learn]

I spoke a little too soon. I am having trouble getting the correct % when sales are below the first range 75% or higher than 125% the formula wants to display the % of commission at 75-99% or 100-125%. I have tried adjusting the formula adding a larger range but still get the same result. Am I missing something.

 

[Aladin Akyurek]

I spoke a little too soon. I am having trouble getting the correct % when sales are below the first range 75% or higher than 125% the formula wants to display the % of commission at 75-99% or 100-125%

Would you be a bit more specific:

Note and post here what A2:A1 yields and along with wat this formula bit

=LOOKUP(A2/A1,{0;0.75;1;1.26},{0.1;0.12;0.15;0.2})

returns. And also what you expect from the latter.

 

[quick2learn]

if A1 is 1000
and
A2 = 749 the display should be 74.9
A2 = 750 to 999 display should be 90 to 119.88
A2 = 1000 to 1250 display should be 150 to 187.50
A2 = 1251 or greater display should be 250.2 or greater

my commission is based on achieving a goal the better I do the higher the pay scale. Currently I am just seeing the results as a % of A1

 

[Aladin Akyurek]

if A1 is 1000
and
A2 = 749 the display should be 74.9
A2 = 750 to 999 display should be 90 to 119.88
A2 = 1000 to 1250 display should be 150 to 187.50
A2 = 1251 or greater display should be 250.2 or greater

my commission is based on achieving a goal the better I do the higher the pay scale. Currently I am just seeing the results as a % of A1

Can't we stay with the initial specs?

Specify A1 and A2

and evaluate what this formula

=LOOKUP(A2/A1,{0;0.75;1;1.26},{0.1;0.12;0.15;0.2})

returns and say whether it's a correct percentage?

 

[quick2learn]

I must have started drinking too early today. haha The first solution works great. I don't know why the % looked wrong earlier. Sir you are a true MVP. Thank you again.

 

[Aladin Akyurek]

I must have started drinking too early today. haha The first solution works great. I don't know why the % looked wrong earlier. Sir you are a true MVP. Thank you again.

You are welcome. Relieved... Glad to hear that it works as intended.