Excel 2007 : Commission Calculation

I am sure I am missing something really simple or maybe I'm not, but I just cant get this to work.

We institued a new comp plan for our sales people. They have a tiered commission plan and as they meet each level the higher their commission rate. My problem is taking into account when they are between levels.

This is the schedule:
Pct Quota Comm Amt Delta
0% 0.50% $- 0.50%
20% 0.75% $10,000 0.25%
40% 1.00% $20,000 0.25%
60% 1.25% $30,000 0.25%
80% 1.50% $40,000 0.25%
100% 2.00% $50,000 0.50%

When we changed to this program I had an employee that had sold $7680.
So when you calculate her commission on a new sale of $30,000 she should receive $28,260.

10,000-7,680=2320*.5=1160
10000*.75=7500
10000*1.0=10000
7680*1.25=9600
Total Commission 28260

Can someone look at the attached and tell me what I am doing wrong?

Welcome to the Board.

What you have in the file already will work but you need to make a few corrections as there are a few erroneous references in place...

D3: =D3-N(D2)
copy down

D14: =SUMPRODUCT(($C14>=$C$3:$C$8)*($C14-$C$3:$C$8)*$D$3:$D$8)-SUM(D$13:D13)
copy down

the above once corrected will generate the correct commission per sale figures in D.