test a range of dollar amounts to assign a commission value

[Brooks]

I am developing a commission scale for multiple sales people. I would like
my staff to only be able to enter what they sold (membership type) and have
the commission value be input into a different column. Each membership type
has a total value associated with it which is the value the commission is
based on. I would like to be able to have an entire column with a formula
assigned to it where the commission value would be placed.

The commission is based on a scale for different dollar amounts.
example:

value total $
1.2 >620
1 580-620
0.7 445-579
0.5 325-444
0.3 181-324
0.1 <181


So I would like the formula to test the total value and enter the
appropriate commission value.


Thank you for any help.

 

[Gord Dibben]

Enter in C1 and copy down.

=LOOKUP(B1,{0,181,325,445,580,620},{0.1,0.3,0.5,0.7,1,1.2})

Or delete column A and enter the formula in B1 and change to

=LOOKUP(A1,{0,181,325,445,580,620},{0.1,0.3,0.5,0.7,1,1.2})


Gord Dibben MS Excel MVP

 

[Joel]

=HLOOKUP(A1,{0,181,325,445,580,620;0.1,0.3,0.5,0.7,1,1.2},2,TRUE)

 

[Brooks]

I'm getting an error #n/a when I enter the formula. All the sales are
entered into an individual row and the column that has the value starts at
L5. The cells where the point value is assigned starts at R5.

 

[Joel]

You will only get N/A if you have a value less than zero. Make sure the
numbers you look up are not negative.

 

[Gord Dibben]

I'm not sure I understand your sheet layout so I will guess

The sales figures are in Column L starting at L5 and running down.

Enter in R5 =LOOKUP($L5,{0,181,325,445,580,620},{0.1,0.3,0.5,0.7,1,1.2})

Copy down column R as far as you have numbers in column L.

You don't need a list of the commission values. They are enclosed in the
formula.

i.e. {0.1,0.3,0.5,0.7,1,1.2}


Gord

 

[Gord Dibben]

Good point Joel.

Something I did not think of.


Gord