Commision Calculation Formula that has stumped me.

Hi:
My company has a sales commission plan that has really stumped me to create
a formula to fully work. Thought this might be a quick answer for someone.

The Commission Plan is as Follows:

Year to Date Sales Levels Commission Amount for Individual Sale
$0 to 899,999 1%
900,000 to 1,499,999 2%
1,500,000 to 2,599,999 3%
Greater than 2,600,000 4%

The formula below semi-works....BUT...the problem is that it doesn't
calculate the "over the level" amount properly, for example:
It it is January 1 and my salesperson has $0 sales so far for the year. He
sells a $950,000
sale as his first sale. He is paid 1% of the sale amount between $0 to
899,999 and 2% on the amount over $900,000. (it has to split the sale and
the formula below doesn't). He now has a year to date sales amount of
$950,000. His next sale is a $2,000,000 sale making his year to date sales
now $2,950,000. He should be paid on this second sale: 2% on the amount
between $950,000 and 1,499,000, 3% on the amount between 1,500,000 and
2,599,999, and 4% on the amount over 2,600,000.

See the plan???? It has to be fully calculated on each individual sale.

Here is the formula that is the basic version I did that does not work on
the split sales.
T45 is the year to date sale level. K45 is the individual sale.

=ROUND(IF(AND(T45>=0,T45<=899999),K45*0.01,IF(AND(T45>=900000,T45<1499999),K
45*0.02,IF(AND(T45>=1500000,T45<=2599999),K45*0.03,IF(T45>2600000,K45*0.04,0
)))),0)

And we only pay commission in full dollars which is why I have the rounding.

I really would appreciate a solution to this. Maybe it's very simple but has
stumped me and added a few grey hairs.

Thanks a million in advance!!!!
Please also reply an answer via email as I only check this board once a week or so.

Tom Power
email: tpower@tpower.net or tpower@columbus.rr.com