Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup function
I have a sheet listing comission rate eg. sales less than $200, the rate is
..5%, less than 300, the rate is 1%..etc. Then i have another table showing different sales value of different workers. How do i find the commission rate per worker using lookup functions?? Please help me..thx a lot |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup function
Hi
You could set up a table and use a VLOOKUP function. Post your breakdowns and we'll have a go!! Andy. "Anh" wrote in message ... I have a sheet listing comission rate eg. sales less than $200, the rate is .5%, less than 300, the rate is 1%..etc. Then i have another table showing different sales value of different workers. How do i find the commission rate per worker using lookup functions?? Please help me..thx a lot |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup function
It's lika table like this:
Sales Comission rate 200 and under 0.50% 300 and under 0.75% .................................................. .... 1000 and under 2.50% Then i have a list of different people with different sales figures: Name Sales Comission rate Anthony 487 ? Bill 568 ? ....................................... (up to 15 people) I need to find the comission rate for each people by using lookup & the table above. I do not know how to do it... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup function
Hi Anh,
See Debra Dalgleish's VLookup tutorial at: http://www.contextures.com/xlFunctions02.html --- Regards, Norman "Anh" wrote in message ... It's lika table like this: Sales Comission rate 200 and under 0.50% 300 and under 0.75% .................................................. ... 1000 and under 2.50% Then i have a list of different people with different sales figures: Name Sales Comission rate Anthony 487 ? Bill 568 ? ...................................... (up to 15 people) I need to find the comission rate for each people by using lookup & the table above. I do not know how to do it... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup function
Perhaps a sample construct for your situation would also help?
http://cjoint.com/?mzk3YMTmpY Anh_wks.xls Assuming the commission table is in Sheet1, in A1:B6 Sales Comission rate 200 0.50% 300 0.75% 500 2.50% 750 3.00% 1000 3.50% And in Sheet2, sales are listed in B2 down Name Sales Comission rate Anthony 487 Bill 568 George 250 Peter 855 Goliath 1356 Put in C2, copy down: =IF(B2="","",VLOOKUP(B2,Sheet1!A:B,2,TRUE)) For the sample, we'd get the resulting comm rates in col C: Name Sales Comission rate Anthony 487 0.75% Bill 568 2.50% George 250 0.50% Peter 855 3.00% Goliath 1356 3.50% (The above presumes a simple "flat" comm. rate structure, not a complex "tiered" one) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Anh" wrote in message ... It's lika table like this: Sales Comission rate 200 and under 0.50% 300 and under 0.75% .................................................. ... 1000 and under 2.50% Then i have a list of different people with different sales figures: Name Sales Comission rate Anthony 487 ? Bill 568 ? ...................................... (up to 15 people) I need to find the comission rate for each people by using lookup & the table above. I do not know how to do it... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup function
Pl disregard this line:
(The above presumes a simple "flat" comm. rate structure, not a complex "tiered" one) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup function
Why leave that out? That was a useful addition.
Maybe not in layman's terms, so maybe you could provide an example of both. -- Kind regards, Niek Otten "Max" wrote in message ... Pl disregard this line: (The above presumes a simple "flat" comm. rate structure, not a complex "tiered" one) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup function
"Niek Otten" wrote
Why leave that out? That was a useful addition. .... Pl disregard this line: (The above presumes a simple "flat" comm. rate structure, not a complex "tiered" one) My 2nd thought then was the line might arouse confusion rather than add clarity. Didn't want that to happen, certainly not on Christmas day, Niek <g Maybe not in layman's terms, so maybe you could provide an example of both. Ah, think it's much better (and easier) to point to JE's excellent treatment on the subject at his: http://www.mcgimpsey.com/excel/variablerate.html or, the direct link to the commissions example at: http://www.mcgimpsey.com/excel/varia...ml#commissions Merry Christmas, Niek ! -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup function
Same to you, Max
Excellent link Merry Christmas to all readers! -- Kind regards, Niek Otten "Max" wrote in message ... "Niek Otten" wrote Why leave that out? That was a useful addition. ... Pl disregard this line: (The above presumes a simple "flat" comm. rate structure, not a complex "tiered" one) My 2nd thought then was the line might arouse confusion rather than add clarity. Didn't want that to happen, certainly not on Christmas day, Niek <g Maybe not in layman's terms, so maybe you could provide an example of both. Ah, think it's much better (and easier) to point to JE's excellent treatment on the subject at his: http://www.mcgimpsey.com/excel/variablerate.html or, the direct link to the commissions example at: http://www.mcgimpsey.com/excel/varia...ml#commissions Merry Christmas, Niek ! -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup function
For the link Jones gave me, i already had a look..but in my case the table
showed the upper limit, not lower limit so the approximate match of Vlookup did not return the correct answer.. And for the link Max gave me..it's very useful but quite far complicated...In my case it's not marginal rate...and the answer u suggested me seems not work..it keep on turn out to be #VALUE...:( |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup function
For the link Jones gave me, i already had a look..but in my case the table
showed the upper limit, not lower limit so the approximate match of Vlookup did not return the correct answer.. And for the link Max gave me..it's very useful but quite far complicated...In my case it's not marginal rate...and the answer u suggested me seems not work..it keep on turn out to be #VALUE...:( |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup function
Put this table in A1:B4
0 0.50% 200.000001 0.75% 300.000001 2.50% 1000 100% With the sales amount in C1: =VLOOKUP(C1,$A$1:$B$4,2) -- Kind regards, Niek Otten "Anh" wrote in message ... For the link Jones gave me, i already had a look..but in my case the table showed the upper limit, not lower limit so the approximate match of Vlookup did not return the correct answer.. And for the link Max gave me..it's very useful but quite far complicated...In my case it's not marginal rate...and the answer u suggested me seems not work..it keep on turn out to be #VALUE...:( |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup function
Layout a bit messed up
So, column A: 0 200.000001 300.000001 1000 Column B: 0.50% 0.75% 2.50% 100% -- Kind regards, Niek Otten "Niek Otten" wrote in message ... Put this table in A1:B4 0 0.50% 200.000001 0.75% 300.000001 2.50% 1000 100% With the sales amount in C1: =VLOOKUP(C1,$A$1:$B$4,2) -- Kind regards, Niek Otten "Anh" wrote in message ... For the link Jones gave me, i already had a look..but in my case the table showed the upper limit, not lower limit so the approximate match of Vlookup did not return the correct answer.. And for the link Max gave me..it's very useful but quite far complicated...In my case it's not marginal rate...and the answer u suggested me seems not work..it keep on turn out to be #VALUE...:( |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Function help | Excel Discussion (Misc queries) | |||
Lookup Function Problems | Excel Worksheet Functions | |||
Complicated lookup function | Excel Worksheet Functions | |||
lookup function 1 | Excel Worksheet Functions | |||
How do I use 3 cells to create the string for a lookup function? | Excel Worksheet Functions |