Forum Discussion
Help with excel formula please
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?
In L9:
=E9*PRODUCT(IFS(F9:I9="100% >",105%,F9:I9="96-99%",97.5%,F9:I9="91-95%",95%,F9:I9="<90%",90%))
HansVogelaar 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:
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?
