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 

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?

[#3]

@Jason1985 

Let me add to the question that @Hans Vogelaar 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.

[#4]

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

[#5]

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.

[#6]

@Jason1985 

In L9:

=E9*PRODUCT(IFS(F9:I9="100% >",105%,F9:I9="96-99%",97.5%,F9:I9="91-95%",95%,F9:I9="<90%",90%))

[#7]

@Jason1985 

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. @Hans Vogelaar  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 @Hans Vogelaar 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. 

[#8]

@Hans Vogelaar Thanks for the reply and help.

I think we are nearly there with what you have, but the final total in column L9 is not calculating correctly.

For example: say each of the separate x4 KPI columns achieve > 100% it should be adding 5% to the base bonus in cell E9 for each selected cell from F9:I9 that is 100% >, but the calculations seem off.

Equally if they fail to hit the KPI's and achieve < 90% it should be deducting 5% for each column F9:I9 but the final bonus total in cell L9 is not quite correct

I have attached a screen shot of my current worksheet with your formula included in cell L9, and the example if the team member failed to achieve all 4 KPI's i.e. < 90% then 5% should be deducted for each of the F9:I9 from E9 and the total value outputted in cell L9, but it is giving a final total of £236.20, when by my calculations £360 (cell E9 base bonus) - 20% (5% for each failed KPI F9:I9) =£288 TOTAL Bonus due, yet it is outputting this final total in cell L9 as £236.20: 

[#9]

@Jason1985 

Could you attach a sample workbook (without sensitive data) demonstrating the problem, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

[#10]

@Hans Vogelaar Please see attached for a copy of the workbook I am using for this project, in it's entirety.

[#11]

@Jason1985 

Thanks, I had interpreted your request slightly differently.

Does the attached version do what you want?

[#12]

@Hans Vogelaar Thanks! but still not calculating correctly, for instance as an example I put 3 out of the 4 KPI columns at achieving > 100% which should apply 5% to the value in cell E9 into L9, and then I put the last KPI as only achieving < 90% which should deduct 10% but it is calculating the final total as £378 in cell L9, when it should be £414 in cell L9 for final total (i.e. cell E9 (base bonus of 5%) £360 + x3 KPI's at 15% (5% each)

Sorry, very close!

[#13]

@Jason1985 

Could you explain why the "correct" answer should be 414. My spreadsheet agrees with that of @Hans Vogelaar and it's demonstrated below. Adding 5% of the base for each of the first three KPIs and then deducting 10% (again, 10% of the base) at the end. That yields 378.  And that would be true regardless of the sequence of the scoring of the KPIs. If you are doing it in a way that cumulatively adds, say 5% to 360, then 5% to 378, then 5% of that result to that result, and then subtracts 10% from that cumulative total, it would vary if you started with the 10% deduction. Or did it in the middle. That would be somewhat capricious,, don't you think?!

So please explain your system rather than just saying "the correct answer is 414."

[#14]

@Jason1985 

when it should be £414 in cell L9 for final total (i.e. cell E9 (base bonus of 5%) £360 + x3 KPI's at 15% (5% each)

I can only get the final result of 414 by ignoring the final KPI of <90%, and only multiplying the 360 base by 15% and adding that result to the base.  So your 414 appears to ignore that 10% reduction.

Hence my request that you provide us with a better explanation of your process.

I was able to produce an end value of 414 only by ignoring the final KPI of <90% and calculating the modification to the base bonus by multiplying, as you say in that quoted sentence above, by 15% (the sum of the three positive 5% modifications). I can get there--to 414--by two different routes, in either case only by ignoring your reduction of 10% caused by the final KPI rating of <90%.  (The example in the middle just takes a different, and clearly not what you intend, sequence of mathematical operations.)

Unless I'm mistaken, the way to accomplish your goal is still that which @Hans Vogelaar and I have suggested. We do it differently ourselves, in that mine is meant to make more obvious the subordinate steps (each individual KPI percentage modification made visible before the adding to get the final result).

Are he and I missing something here? Or did you unintentionally omit the KPI of <90% in your calculation that produced 414?

[#15]

@Jason1985 

In reviewing your series of posts--sorry to be a nag here--I just noticed a discrepancy that may be part of what's behind our shared confusion.

Your first post said this:

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

Your later post says

Equally if they fail to hit the KPI's and achieve < 90% it should be deducting 5% for each column F9:I9 but the final bonus total in cell L9 is not quite correct

Which is it?

[#16]

@mathetes So, let me make myself more clearer to prevent any further confusion or ambiguity.

Following a detailed discussion between myself and a colleague we both had interpreted this differently, we have now mutually agreed what the end goal should look like, see below:

Cell E9 is 5% of c9

Cell F9, G9 and H9 are all worth 5% of D9 if 100% is achieved - 95% till 99.9 will result in 2.5% being removed from L9 (Total Bonus), 90% to 94.9% will result in 5% being removed from L9, Below 90% will result in 10% being removed from L9

Cell I9 is worth 5% of D9 if 100% is achieved - Anything less than 100% will result in 50% of total bonus being removed.

Hope this helps provide some clarity for all the confusion...

I have attached a sample of the workbook

[#17]

@Jason1985 

We're getting to greater clarity, although it still would help if you worked through (as if on a white board or black board) a few examples. As you'll see in the following, there are still ambiguities, and having you work through a few examples of different scenarios (with explanation) would help even further. It's clear in your minds now, but greater clarity still could be achieved with examples, not just the 'rules' of how the bonus is to be calculated.

That said, let me ask a few questions to make sure I'm getting each of these other points you've made.

Cell E9 is 5% of c9

Clear enough.

Cell F9, G9 and H9 are all [each] worth [an additional] 5% of D9 [Don't you mean E9??] if 100% is achieved

And presumably applied to L9 (that's a question)

- 95% till 99.9 will result in 2.5% being removed from L9 (Total Bonus), 90% to 94.9% will result in 5% being removed from L9, Below 90% will result in 10% being removed from L9

Are these removals to be calculated based on the value of L9 after whatever increases might have been earned for 100% achievements in the prior step? Or are these percentages calculations based on the value in E9?

Cell I9 is worth 5% of D9 if 100% is achieved - Anything less than 100% will result in 50% of total bonus being removed.

Now this statement really needs clarification. Again, I'm sure it's clear in your own mind, but notice, D9 in your spreadsheet shows a value of 1,150, 5% of which would be 52.50, which is fine, but very different from 5% of E9. And is the "Anything less than 100% will result in 50% of total bonus being removed" to be applied to the bonus in L9? And does all that get applied to L9 after all of the foregoing calculations have been completed?

Please: provide several examples of different ratings and how they influence the calculations of bonus. Feel free to use your spreadsheet as the white board, but add a few comments to explain how you're getting from raw data to final conclusion.  Note: I'm not asking you to use Excel formulas or function to explain, just use the cells as repositories for numbers, with comments explaining how you calculated them. @Hans Vogelaar or I can translate the comments into formulae.

In case this seems like nit-picking, it's because we're not able to sit down face-to-face, so questions like the ones I've asked above , or interpretations like I've interposed above, are what we'd be doing IF we were able to meet face-to-face. I'm quite confident the end result will be relatively simple, but we need to get the system systematized.

[#18]

So to clarify further...

Employee X gets a monthly set target (Cell B9)

We will then at month end enter their actual sales figure (cell C9)

This will then autocalculate any variance between target vs achieved in cell D9

All employees who hit their Rev sales target then gets 5% base bonus of their ACTUAL sales achieved

they then can earn even more £££, dependent on achieving 4 key separate KPI's namely: NEW, 45%>unlimited mix, 2nd line attachment, HBB

However we obviously want them to strive to achieve these key company KPI targets, and then penalised financially for failing to achieve,

So in terms of rewarding them for achieving any of the 4 KPI's we do this as below:

-IF they hit any of the KPI's they will then get 5% of the variance over their target vs actual sales, namely cell D9,

-IF they nearly achieve a KPI and only get 95-99.9% of then they will lose 2.5% of their calculated TOTAL bonus (cell L9)

-IF they only achieve 90-94.9% of a KPI, they will lose 5% of their TOTAL bonus (cell L9)

-IF they achieve anything less than < 90%, then this will result in 10% being removed from their TOTAL bonus (Cell L9)

-Notably though whilst their 'HBB' Kpi is worth the same 5% of any variance over target in cell (D9) IF achieved, HOWEVER, for this one anything less than < 100% for this HBB KPI will result in 50% of their TOTAL bonus being removed (cell L9)

Hopefully this is more succinct, thanks!

[#19]

@Jason1985 

I've asked several times for you to work through several examples. You have yet to do so. Doing so would help put flesh on the bones of your description of the rules to be applied. Again, as I said the last time, I'm NOT asking that you resolve it in Excel; that's what you're asking us to do. I am asking you to work through several examples as if you were illustrating the process on a white board. Several examples. Showing your work (as our mathematics teachers in high school might have said). That is, show the calculations and how they follow upon one another toward the final result.

You've already acknowledged that you and a colleague had different interpretations of what you're being asked to do. That's why it's important to do all we can to remove any remaining ambiguities. Actually working through the application of these rules, applying several different ratings, would help do that.

My intention here is not to be difficult. I'm not sure what I can do to be more succinct with my request, which I think is entirely reasonable.

[#20]

Look forget it!!!