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

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

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...

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...

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...

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

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

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

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

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...:(

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...:(

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...:(

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...:(