Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)))))))) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
commission percentages | Excel Worksheet Functions | |||
Formula for Commission | Excel Worksheet Functions | |||
Commission Formula | Excel Discussion (Misc queries) | |||
Commission Calculation | Excel Worksheet Functions | |||
Help with Commission forumlas | Excel Worksheet Functions |