Numbers: Income-based pension rate and contribution formula

Numbers Help!

I am creating a spreadsheet for my monthly income as this varies month to month depending on shifts. This impacts things like tax and pension depending on monthly income etc.

I am trying to create a formula that will automatically pick the appropriate pension rate based on my monthly income, but there are six different rate brackets.

For example:

Income of £0-£250 --> pension rate 5%

Income £251-£500 --> pension rate 7%

Income of £501-£1000 --> pension rate 10%

Income >£1000 --> pension rate 15%

Is there a singe formula I can use that will take the monthly income (which I have already figured out in another table) and apply it to the different pension brackets to give me the pension rate for that month?

• and if possible, to then multiply the rate by the monthly income to give my total pension contribution for that month

Hope this all makes sense, and thanks in advance!