Sales Commission Rates

[jcracch]

I'm trying to calculate a new sales commission based upon a range possible
values.

Sales Rep earns a different commission based upon Margin % on the sale.

A simple version table is this:
From Until
0.0% 2.99% 0.00%
3.0% 9.99% 1.00%
10.0% 14.99% 1.25%
15.0% 19.99% 1.75%
20.0% 25.99% 2.50%
26.0% 3.25%

(The actual table is much more detailed and does not work using Vlookup)

 

[Jacob Skaria]

VLOOKUP should work. With the query % in A10 (in the same format as ColA of
table) try the below formula..Try and feedback

=VLOOKUP(A10,A1:C7,3,TRUE)

If this post helps click Yes

 

[jcracch]

It does not.
There are some additional breaks in the actual table that we use. For
instance, one of our ranges would be 10% - 12.49% and could pay 2%, the next
being 12.5% - 13.5% which could pay 2.25%.
If the margin % on a sale is 12.45%, Vlookup returns 2.25% using the formula
below. (Already tried this one.)

 

[Jacob Skaria]

Try
=LOOKUP(C11,A1:A8,C1:C8)

Also with VLOOKUP 12.45 returns 2.00% . The data and formula are pasted for
reference,. =VLOOKUP(C11,A1:C8,3,TRUE)

From Until
0.00% 2.99% 0.00%
3.00% 9.99% 1.00%
10.00% 12.49% 2.00%
12.50% 13.50% 2.25%
15.00% 19.99% 2.50%
20.00% 25.99% 2.75%
26.00% 3.00%

 

[Mike H]

Hi,

If you have a table with your threshold values in 1 column and the commision
rates in another then that is all you need. From what you describe Jacob's
formula does exactly what you want.

The vlookup provided ignores column 2 because it isn't necessary and looks
for the lookup value in the first column. If it doesn't find an exact match
then the TRUE switch makes it return the largest value that is less than
lookup_value.

If this really soesn't work then you will need to post your detailed table
with lookup values and the result you expect to get.

Mike