commission lookup
 

I have a commision form that return the amount of commission my sales guys
are suppose to get base on percentage of sales value. a represents the high
of the range while b represents the low end of range. c represent the
percentage for that range and d represents the presentage this saleguy got
for this sales. How do I get e to show the value of 9 which is the
percentage for this sale. Thanks for all you help


a b c d e
100.00% 99.991% 10 98.72
99.99% 97.50% 9
97.49% 95.00% 8
94.99% 92.50% 7
92.49% 90.00% 6
89.99% 87.50% 5
87.49% 85.00% 4
84.49% 82.50% 3
82.99% 0.00% 2

--
We all need a little help

commission lookup
 

Hi,

If your prepared to sort your table like this with column 2 ascending then
this formula works

=VLOOKUP(D1,B1:C9,2,TRUE)

82.99% 0.00% 2
84.49% 82.50% 3
87.49% 85.00% 4
89.99% 87.50% 5
92.49% 90.00% 6
94.99% 92.50% 7
97.49% 95.00% 8
99.99% 97.50% 9
100.00% 99.99% 10

Mike

"Pat Rice" wrote:

I have a commision form that return the amount of commission my sales guys
are suppose to get base on percentage of sales value. a represents the high
of the range while b represents the low end of range. c represent the
percentage for that range and d represents the presentage this saleguy got
for this sales. How do I get e to show the value of 9 which is the
percentage for this sale. Thanks for all you help


a b c d e
100.00% 99.991% 10 98.72
99.99% 97.50% 9
97.49% 95.00% 8
94.99% 92.50% 7
92.49% 90.00% 6
89.99% 87.50% 5
87.49% 85.00% 4
84.49% 82.50% 3
82.99% 0.00% 2

--
We all need a little help

commission lookup
 

Or leave it as is and use this formula:
=IF($D1$B$1,10,IF($D1$B$2,9,IF($D1$B$3,8,IF($D1 $B$4,7,IF($D1$B$5,6,IF($D1$B$6,5,IF($D1$B$7,4, IF($D1$B$8,3,2))))))))

commission lookup
 

Mike,

That did it, can you tell me why? What does 2 represent? I get true but and
had tried the formula but didn't have the 2 in it. I am new to this and just
winging.
--
We all need a little help


"Mike H" wrote:

Hi,

If your prepared to sort your table like this with column 2 ascending then
this formula works

=VLOOKUP(D1,B1:C9,2,TRUE)

82.99% 0.00% 2
84.49% 82.50% 3
87.49% 85.00% 4
89.99% 87.50% 5
92.49% 90.00% 6
94.99% 92.50% 7
97.49% 95.00% 8
99.99% 97.50% 9
100.00% 99.99% 10

Mike

"Pat Rice" wrote:

I have a commision form that return the amount of commission my sales guys
are suppose to get base on percentage of sales value. a represents the high
of the range while b represents the low end of range. c represent the
percentage for that range and d represents the presentage this saleguy got
for this sales. How do I get e to show the value of 9 which is the
percentage for this sale. Thanks for all you help


a b c d e
100.00% 99.991% 10 98.72
99.99% 97.50% 9
97.49% 95.00% 8
94.99% 92.50% 7
92.49% 90.00% 6
89.99% 87.50% 5
87.49% 85.00% 4
84.49% 82.50% 3
82.99% 0.00% 2

--
We all need a little help

commission lookup
 

Pat,

=VLOOKUP(D1,B1:C9,2,TRUE)

The formula doesn't use column 1 of your data, it used columns B&C (B1:C9)
Vlookup always looks up the left hand column (B or column 1 in this case) and
returns the column you specify which in this case is 2 or column C. Obvioulsy
the column you specify must be in the range. Specifying column 3 of a 2
coulmn range doesn't work.

When you use the TRUE switch the data must be sorted.

It's actually quite refreshing that someone has asked 'Why'. Often posters
don't and become equally stuck the next time they attempt a similar formula.

Mike

"Pat Rice" wrote:

Mike,

That did it, can you tell me why? What does 2 represent? I get true but and
had tried the formula but didn't have the 2 in it. I am new to this and just
winging.
--
We all need a little help


"Mike H" wrote:

Hi,

If your prepared to sort your table like this with column 2 ascending then
this formula works

=VLOOKUP(D1,B1:C9,2,TRUE)

82.99% 0.00% 2
84.49% 82.50% 3
87.49% 85.00% 4
89.99% 87.50% 5
92.49% 90.00% 6
94.99% 92.50% 7
97.49% 95.00% 8
99.99% 97.50% 9
100.00% 99.99% 10

Mike

"Pat Rice" wrote:

I have a commision form that return the amount of commission my sales guys
are suppose to get base on percentage of sales value. a represents the high
of the range while b represents the low end of range. c represent the
percentage for that range and d represents the presentage this saleguy got
for this sales. How do I get e to show the value of 9 which is the
percentage for this sale. Thanks for all you help


a b c d e
100.00% 99.991% 10 98.72
99.99% 97.50% 9
97.49% 95.00% 8
94.99% 92.50% 7
92.49% 90.00% 6
89.99% 87.50% 5
87.49% 85.00% 4
84.49% 82.50% 3
82.99% 0.00% 2

--
We all need a little help

commission lookup
 

Well, I am not one of those people, I like learning and will print out this
post so I don't have to ask again. Thanks again for all your help. You have
made my day brighter.
--
We all need a little help


"Mike H" wrote:

Pat,

=VLOOKUP(D1,B1:C9,2,TRUE)

The formula doesn't use column 1 of your data, it used columns B&C (B1:C9)
Vlookup always looks up the left hand column (B or column 1 in this case) and
returns the column you specify which in this case is 2 or column C. Obvioulsy
the column you specify must be in the range. Specifying column 3 of a 2
coulmn range doesn't work.

When you use the TRUE switch the data must be sorted.

It's actually quite refreshing that someone has asked 'Why'. Often posters
don't and become equally stuck the next time they attempt a similar formula.

Mike

"Pat Rice" wrote:

Mike,

That did it, can you tell me why? What does 2 represent? I get true but and
had tried the formula but didn't have the 2 in it. I am new to this and just
winging.
--
We all need a little help


"Mike H" wrote:

Hi,

If your prepared to sort your table like this with column 2 ascending then
this formula works

=VLOOKUP(D1,B1:C9,2,TRUE)

82.99% 0.00% 2
84.49% 82.50% 3
87.49% 85.00% 4
89.99% 87.50% 5
92.49% 90.00% 6
94.99% 92.50% 7
97.49% 95.00% 8
99.99% 97.50% 9
100.00% 99.99% 10

Mike

"Pat Rice" wrote:

I have a commision form that return the amount of commission my sales guys
are suppose to get base on percentage of sales value. a represents the high
of the range while b represents the low end of range. c represent the
percentage for that range and d represents the presentage this saleguy got
for this sales. How do I get e to show the value of 9 which is the
percentage for this sale. Thanks for all you help


a b c d e
100.00% 99.991% 10 98.72
99.99% 97.50% 9
97.49% 95.00% 8
94.99% 92.50% 7
92.49% 90.00% 6
89.99% 87.50% 5
87.49% 85.00% 4
84.49% 82.50% 3
82.99% 0.00% 2

--
We all need a little help