Help with excel formula please

[Jason1985]

Hi,

So I am trying to create a new bonus spreadsheet,

Say an employee gets a base bonus of 5% in cell E9,

I then want to create a drop down over 4 columns in cells F9:I9, containing percentage ranges and then an attached calculation to each selection from the dropdown and this to automatically update the amended final total in cell L9

If '<90%' is selected from the drop down, then I want to deduct 10% from cell E9, and update the total in cell L9

If '91-95%' is selected from the drop down, then I want to deduct 5% from cell E9, and keep updating the total in cell L9

If '96-99%' is selected from the drop down, then I want to deduct 2.5% from cell E9, and update the total in cell L9

If they achieve '100% >' then I want to add 5% from cell E9 update the total in cell L9

I have tried IF statement formula and it works great but ONLY updates based when an individual column  is selected and does not amend the final total cumulatively. This is what I currently have: 

=IF(OR(F9:I9="< 90%"),E9*0.9,IF(OR(F9:I9="91-95%"),E9*0.95,IF(OR(F9:I9="96-99%"),E9*0.975,IF(OR(F9:I9="100% >"),E9*1.05))))

Any help would be greatly appreciated it is driving me crazy.

Many thanks

J

[#2]

@Jason1985 

Look forget it!!!

If you insist.

I'm disappointed, because it would have been satisfying to work it through with you. I wish you luck in resolving it yourself. I will say, I expect the formulas are pretty simple once it's clear what they're to do. So since it's so clear in your own mind, you should have no trouble. Best wishes.