Forum Discussion
Help with excel formula please
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?
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
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.
HansVogelaar Please see attached for a copy of the workbook I am using for this project, in it's entirety.
