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)

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
---------------
Jacob Skaria


"jcracch" wrote:

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)

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" wrote:

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
---------------
Jacob Skaria


"jcracch" wrote:

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)

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%


--
If this post helps click Yes
---------------
Jacob Skaria


"jcracch" wrote:

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" wrote:

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
---------------
Jacob Skaria


"jcracch" wrote:

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)

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


"jcracch" wrote:

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" wrote:

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
---------------
Jacob Skaria


"jcracch" wrote:

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)