Forum Discussion
Help with excel formula please
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.
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.