I am trying to build a bonus calculator based on a tiered/stepped outcome.

For example.

An employee’s salary is 30,000 their max potential is 20% oftheir salary = 6000

If company profit is 3.5 million pay out 15% of a persons maxpotential

At 7million pay out an additional 30% of a persons max potential

At 10.3 million pay out an additional 15% of a persons max potential.

In total the company profit would equate to 60% of the max potential. In between the above steps then it will pay our proportionally (this is the bit I’m stumped at)

I have tried IF/AND statements but can’t quite get it to match, I’ve also had a look at tiered commission but again can’t tweak it to my purpose.

I’m after a formula (or a few, I’m not picky) that would ping out the % of the 60% which I can apply to everyone.

Hope that makes sense and thanks in advance