Forum Discussion
Help with excel formula please
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
20 Replies
Let me add to the question that HansVogelaar has asked.
My question is "Why do you have four drop downs to begin with?"
Your formula reads as if you're really looking for a single percentage range to be selected, with a single calculation to be done based on whichever one of the several conditions exists. If that's the case you only need a single drop down with each of the possibilities listed once. And your formula would be a LOT simpler.
Now, to put my question in context, I'm asking as a former (now retired) HR director for a division of a major corporation....I created (Lotus) spreadsheets to do this kind of salary computation back in the early 1980s....(possibly, in other words, before you were born!!). It just seems that you may be making this more complicated than it needs to be. It's also possible that I'm misunderstanding your description and the many-layered IF function you gave us.
- The reason I have 4 different columns is they relate to 4 different sales KPI’s. Hence why I am not sure it can be made any simpler, as each individual KPI has its own target set.
The reason I have 4 different columns is they relate to 4 different sales KPI’s. Hence why I am not sure it can be made any simpler, as each individual KPI has its own target set.
That is what wasn't made clear (or it's what I missed). I saw no reference to four different KPIs in the original post, only to four drop downs, with no reason given for them.
I have created a possible way to do it, using VLOOKUP and a table for the percentages. HansVogelaar has given you a different approach.
In this example of mine I just calculate what each drop-down evaluation would do (subtract from or add to that base bonus) and then do a single add of the results in cell L9. The key formula is this:
=$E$9*(VLOOKUP(F8,$F$1:$G$5,2,1))
The result looks like this:
If this is NOT what you want it to do, then, for the sake of being completely clear, can I ask you to clarify how you envision these four adjustments working together. Could you actually work through a complete example or two in plain English and math, but not in Excel. As if you were on a blackboard (or whiteboard--your choice) so that HansVogelaar and I can see how this algorithm plays out as we would if talking with you in person.
Include whatever base salary is involved as well, if that gets affected. I assume that the end result, that base bonus of 5% is applied to the base; that's why I ask. So I'm wondering, for example, if poor performance on each of the four KPIs can result in a reduction of base salary....and so forth.
What do you want to do if, for example, you select '100% >' in all four dropdowns?
Or if you select '<90%' in the first one, '91-95%' in the second one, and leave the other two blank?
- If 100% > is selected in all 4 columns then the additional 5% would apply for each. Equally if <90% is selected in the first one then 10% would be deducted off the running total I am after in cell L9, and the total would keep being amended based on the selection whether that be the deduction or the addition for achieving 100% or greater. Thanks
In L9:
=E9*PRODUCT(IFS(F9:I9="100% >",105%,F9:I9="96-99%",97.5%,F9:I9="91-95%",95%,F9:I9="<90%",90%))
