Excel help required

[JoeBloggs]

Annual Bonus Rate 3% 3% £100,000.00

4% £115,000.00

5% £125,000.00

6% £140,000.00

Annual Banked Target £150,000 7% £150,000.00

 

 

When someone puts a figure in box B5 (banked target) I want it to change the percentage to whatever it should be according to the right. So less than £100k = 0%, £100k-£114,999.99 = 3% and so on

simple formula.xls

[CESIL]

Try this =IF(B5>=100000,VLOOKUP(B5,D1:E5,2,TRUE),0)

[Steve21]

If you don't mind it being a bit long-winded

 

=IF(B5>=F5,E5,IF(B5>=F4,E4,IF(B5>=F3,E3,IF(B5>=F2,E2,IF(B5>=F1,E1,0)))))

 

But could do it fancy with look-ups etc

 

Steve

[pcstru]

Use vlookup. Put the %'s in column G (at the other side of the £ figures). The % can then be looked up by putting =vlookup(B5,F1:G5,2,TRUE) in cell B1. The TRUE param tells vlookup to make an inexact match, it will match to the closest figure below whatever you put in B5. So £114,000 will match to 3%, anything above £150K will match to G5 and anything below £100K will return N/A.

[JoeBloggs]

thanks guys got it working