Help with commission formula

[rebeccapw]

Hi - I'm completely stumped with this - I am trying to work out a formula for the following commission scheme:

£ 0-1999 - 0 commission
£ 2000-2001 - 2.5% commission (ie. flat £50 bonus)
£ 2001-3000 - 4% commission
£ 3001-4000 - 6% commission
£ 4001-5000 - 7% commission
£ 5001-6000 - 8% commission

The figure I'm using as an example is £3576.01 and the commission payable should be £ 124.56. All of the formulas I've tried give me the wrong answer as they are putting 4% commission on the whole amount, and it should be 2.5% on the first £2000, 4% on the next £1000, and 6% on £576.01 etc. I have tried a SUMPRODUCT formula, a MAX formula (which gets the closest answer) and a LOOKUP formula.

Thanks!

[Fotis1991]

INDEX & MATCH can help you

[Pepe Le Mokko]

The differential rates technique described here uses SUMPRODUCT.

[etaf]

lookup or sumproduct or a nested IF should work

nested if
=IF(A2>6000,300,IF(A2>5000,220+((A2-5000)*0.08),IF(A2>4000,150+((A2-4000)*0.07),IF(A2>3000,90+((A2-3000)*0.06),IF(A2>2001,50+((A2-2000)*0.04),IF(AND(A2>=2000,A2>=2001),50,0))))))

there are a few other ways to do this , maybe as an array lookup or a lookup/vlookup table

see attached

[rebeccapw]

Thanks etaf - that works!

[etaf]

your welcome