General Chat Thread, Excel help required in General; ...
1. ## Excel help required

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

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

3. ## Thanks to CESIL from:

JoeBloggs (21st December 2011)

4. 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

5. ## Thanks to Steve21 from:

JoeBloggs (21st December 2011)

6. 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.

7. ## Thanks to pcstru from:

JoeBloggs (21st December 2011)

8. thanks guys got it working

SHARE: