I didn't set this system up, so please don't shoot me.


Salesguys are paid monthly based on commission. Rates change based upon
sales levels reached cumulatively for the year.

Rates are as follows
$1 - $100,000 30%
$100,001 - $300,000 33%
$300,001 - up 35%


Here is where the problem is. Sales are tracked monthly and checks are
cut. Say in the first month a salesman sells $60,000, his commission
would be 30% of $60,000 as he falls into the first category.

The next month, he sells $50,000. The commission on the first $40,000
would be at 30% while the remaining $10,000 would be at 33%.

Now, say the next month the guy only sells $1000. His commission would
still be paid at 33% because he is over $100,001 in sales for the year.


I am currently running into two problems. First, most commission
formula/functions that I have found are somewhat regressive. That being,
if you sell under X amount, the entire amount is commissioned at Y% while
if you are over X amount, you are paid Z%. That is not the case here.

The second problem is when trying to do a bunch of IF, AND, statements, I
run into trouble whenever a single month's sales crosses from one tier to
the next.

I would appreciate any input that some of you fine people might be able to
provide.



Demo

email: Demo AT wilsonpages DOT com

(not sure if that cuts down on spam, but heck, it's worth a shot.