Help with formulas to calculate commission post

[Celest]

Hi

I’m trying to work out an appropriate formula to automatically calculate commission. Our commission structure is below;

[TABLE="width: 414"]
<tbody>[TR]
[TD]Commission band £12,500[/TD]
[TD] Percentage [/TD]
[/TR]
[TR]
[TD]0 – 50%[/TD]
[TD]2.5[/TD]
[/TR]
[TR]
[TD]50 – 75%[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]75 – 100%[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]100 – 150%[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]150 – 200%[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]200% +[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]


Commission is calculated as follows based on this person sales being £21k for December and there target being £12,500

[TABLE="width: 672"]
<tbody>[TR]
[TD][/TD]
[TD]Commission band £12,500[/TD]
[TD] Percentage [/TD]
[TD]Commission[/TD]
[/TR]
[TR]
[TD]£0 – £6,250[/TD]
[TD]0 – 50%[/TD]
[TD]2.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£6,250 – £9,375[/TD]
[TD]50 – 75%[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]£9,375 – £12,500[/TD]
[TD]75 – 100%[/TD]
[TD]10[/TD]
[TD]1,250.00[/TD]
[/TR]
[TR]
[TD]£12,500 – £18,750[/TD]
[TD]100 – 150%[/TD]
[TD]15[/TD]
[TD]937.50[/TD]
[/TR]
[TR]
[TD]£18,750 – £25,000[/TD]
[TD]150 – 200%[/TD]
[TD]20[/TD]
[TD]450.00[/TD]
[/TR]
[TR]
[TD]£25,000 +[/TD]
[TD]200% +[/TD]
[TD]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total Due December[/TD]
[TD][/TD]
[TD]2,637.50 [/TD]
[/TR]
</tbody>[/TABLE]


If commission falls under 75% the just get the 2.5% or the 5% depending on where it falls but if it’s over 100% they get commission on there target and any amount over the target is -

ie:
75 – 100% is 12500 = 10% commission
100-150% is 6250 = 15% commission
150 – 200% is 2250 = 20 % commission

12500 + 6250 + 2250 = 21000

Thanks in advance for your help