Tiered Commission Structure

I'm having trouble creating a commission calculation.
The commission schedule is paid on a tiered rate (below), and occasionally there will be recoveries due to client non-payment, which comes out of commission. There is also a budget variance every month. I'd like to have the Monthly Budget in cell A1, the Revenue in cell A2, Recoveries in cell A3 and the Commission (formula) in cell A4. I only need the formula for cell A4, everything else will be entered manually.

Revenue Schedule
$0-$14,000 = 0%
$14,001 - $18,000 = 7.5%
$18,001 - $BUDGET = 10%
$102% BUDGET - Forever = 15%


Thanks for the help!

This didn't work for me, because it didn't recognise the 0% commission for the first 14K. I used a Sales total of 20K, and it's telling me the commission is $2000, when it should only be $500.
You get paid 7.5% on the $2000 between $14001-$18000, which is $300 for the 18K. You then get paid 10% for revenue over $18001. So say you made $20,000. You would get $300 for the 14-18K, and then $200 for the $18001-$20000. The function is just making it 10% on the whole figure, rather than sectioning off.

Does that make sense?

Originally Posted by emily.kell

I'm having trouble creating a commission calculation.
The commission schedule is paid on a tiered rate (below), and occasionally there will be recoveries due to client non-payment, which comes out of commission. There is also a budget variance every month. I'd like to have the Monthly Budget in cell A1, the Revenue in cell A2, Recoveries in cell A3 and the Commission (formula) in cell A4. I only need the formula for cell A4, everything else will be entered manually.
Revenue Schedule
$0-$14,000 = 0%
$14,001 - $18,000 = 7.5%
$18,001 - $BUDGET = 10%
$102% BUDGET - Forever = 15%

Please see my response #6 to your posting at mrexcel.com/forum,
http://www.mrexcel.com/forum/excel-questions/777985-tiered-commission-structure.html

Thanks very much Joe! Your solution works, though I did find a solution on MrExcel which may work a bit better for my needs. It's great to have two solutions that both work, and are easy to understand!