Tiered Commissions..

**trosasco** · 09-17-2012, 02:40 PM

I'm working on a spreadsheet where I need to calculate monthly commissions based on a tiered table and I can't figure out how to create this formula. My table should calculate the following:

0 - 10 new clients = $50 per client
11 - 20 = $75 per client
21 - 30 = $100 per client
31 - 40 = $125 per client
41 - 50 = $150 per client
51 - 60 = $175 per client
61 - 70 = $200 per client
71 - 80 = $225 per client
81 - 90 = $250 per client
91 - 100 = $275 per client

can someone please help me? I need to do this as soon as possible...

Thank you,
TR

**Pepe Le Mokko** · 09-17-2012, 02:55 PM

Attached an example using differential rates

diff_Rates.xlsx

**Jester0001** · 09-17-2012, 02:56 PM

I think you are looking for a Lookup function to pull the commissions based upon the ranges in the table.

Take a look at the attached sheet, and view the Lookup table and the commissions calculated in the data table using the Lookup function.

Comission.xlsx

Is this what you're looking for?

**trosasco** · 09-17-2012, 02:59 PM

thank you Pepe.. question on column A though... wouldnt it be 0, 11, 21, 31, 41.. instead of 0, 10, 20, 30, 40.. since the tier changes from 11-20 or 21-30? Just a lil confused, Im sorry

**Pepe Le Mokko** · 09-17-2012, 03:15 PM

If you see the formula at work ( using the evaluate formula tool) that the first part( F1>...) would return 0 if the nr of clients was 11. So the 11th client would be paid at 50 dollars instead of 75

**trosasco** · 09-17-2012, 03:40 PM

Perfect.. thank you Pepe.. this one worked.

**Cutter** · 09-18-2012, 11:50 AM

@ trosasco

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.