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?
- 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%))
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:
