Help with excel formula please

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 HansVogelaar 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?

Jason1985 

Could you explain why the "correct" answer should be 414. My spreadsheet agrees with that of HansVogelaar 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."

HansVogelaar 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!