Commission Schedule

I need formula assistance so as to calculate the correct commission based on the following schedule:


$0 - $49,999 5%
$50,000 - $99,999 10%
$100,000 - $149,999 15%
$150,000 - $199,999 20%
$200,000 - $249,999 25%
$250,000 - $299,999 30%
$300,000 - $300,000 35%

Commission are retroactive to dollar one. For example, if sales for the first period are $54,023.06 that would equate to a commission of $5,402.31 calculated as such:

(49,999*.05) = $2,500
(54,023.06 - 49,999) = $4,023 then: 4,023 *.10 = $402.31

Since the salesperson is in the 10% tier, I need to go back and add an additional 5% on all sales up to that point which would be an additional $2,500 in this example. That would equate to (2,500 + 402.31 + 2,500) = $5,402.31.

I'm having difficulty with the third period. It calculates 5% no matter where you are in the grid. Any help would be appreciated. File attached. Thx.