Trying to Calculate a Tiered Commission System within one formula

So I am having issues with trying to calculate the following:

Annual Rev Quarterly Rev Commission
$4,900,000.00 $1,225,000.00 4000
$5,000,000.00 $1,250,000.00 4000
$5,100,000.00 $1,275,000.00 4000
$5,200,000.00 $1,300,000.00 5000
$5,300,000.00 $1,325,000.00 5000
$5,400,000.00 $1,350,000.00 5000
$5,600,000.00 $1,400,000.00 5000

Starting at $4,900,000 up to 5,1000,000 the employee would get $4,000 for every $100K increase

At 5,200,000 and above, the employee would receive $5,000k for every $100k in increase in addition to the amounts earned at the lower tier..

So at $5,200,000 the total commission earned would be $17k

Does anyone have any thoughts at the cleanest way of doing this?

I've attached a sample spreadsheet showing what I want to do, but I am having issues with the combination of the nested if statements and the floor calculation.

Thanks for any help!

maybe check this site for tiered payment systems...
http://www.mcgimpsey.com/excel/variablerate.html

Just because that's the currently framework that's being discussed..no real reason beyond that.

No - you should not do that. Just continue here with the refined conditions.

Originally Posted by AliGW

No - you should not do that. Just continue here with the refined conditions.

Noted....

So I"ve changed how I want to do this:

Basically I want to have an table that shows the total commission at revenue levels vs the incremental approached I attempted earlier. I will attach a sample spreadsheet, but Here's what I am trying to do:
A B C
AnnualRev QuarterlyRev Total Commission
5,000,000 1,250,000 6000
5,100,000 1,275,000 11000
5,400,000 1,350,000 16000
5,600,000 1,400,000 18000
5,800,000 1,450,000 20000

Basically if Quarterly Revenue is at or between those tiers, I want to return the corresponding total Commission Amount without doing a million nested if statements.

Thank you for you help!

Administrative Note:

We don't expect you to bump threads sooner than 24 hours after your last post or more than once a day - it has been under four hours since you last posted. Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

Where in the workbook is the lookup table?

Originally Posted by AliGW

Administrative Note:

We don't expect you to bump threads sooner than 24 hours after your last post or more than once a day - it has been under four hours since you last posted. Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

No worries...I wasn't going to bump it more than once as that is bad message board etiquette in general, but the post was already on the third page so I was getting a little anstsy. My apologies though.

Originally Posted by AliGW

Where in the workbook is the lookup table?

Yeah I saw that my attachment was missing that key element. I updated the attachment for the Revenue Number and a place where the commission calculation should be placed. In the commission calculation field I showed how I was able to pull off the calculation with if(and) statements, but that solution isn't going to be very feasible as I add more tiers (which we are planning on doing) as it's already a rats nest.

Thank you!

Just getting back to this, given what you put in your sample workbook in post #8 and what you wrote, I'm wondering if, as long as you have that table in E1 to F6, this wouldn't just be the easiest way... =VLOOKUP(C1,$F$2:$G$6,2,TRUE)

EDIT: and you really don't need that section in E1 to E6, just F1 to G6.

Please Login or Register  to view this content.

chooses annual or quarterly column