Hello All,

I am looking for some help on an IF function I am looking to create for a spreadsheet that calculates employee bonus terms, and I'd be really grateful for any responses.

The terms are as follows:-

Basic Salary $40,000 $45,000 $50,000 $55,000 $60,000
Tier One Validation 2.5 2.5 3 3 3
Tier One Bonus Rate 25% 20% 12.50% 12.50% 12.50%
Tier Two Validation 3.5 3.5 4 4 4
Tier Two Bonus Rate 50% 40% 25% 25% 25%



I have a income table below these terms to illustrate how the employee's salary rises (including bonus).
Buisness Income Total Pay
$50,000.00 $40,000
$75,000.00 $40,000
$100,000.00 $40,000
$125,000.00
$150,000.00
$175,000.00
$200,000.00
$225,000.00
$250,000.00

Basically the 'validation tiers' mean, if you are bringing in this multiple of your salary as income for the business, above this you will be entitled to a bonus - i.e if the income earned for the business exceeds $100,000 ($40,000*2.5), the employee will be entitled to 25% of the excess income earned for the business on top of their basic salary, up to a maximum of $140,000, where thereafter, if the employee earns more than $140,000 ($40,000*3.5) in business income, they will then be entitled to 50% of the excess income above this amount as a bonus.

I'm really struggling with the tiered element of the bonus, i.e on the first column only applying the 25% bonus rate to the business income between $100,000 and $140,000, and then 50% bonus rate to the business income of $140,000+

=IF(A9>(C2*C3),(C2+(A9-(C2*C3)))*C4,C2)

I've come up with the above function which works out the bonus if it remained at 25% throughout, but, can't work out how to tier it to take into account the next band of bonus.

Hopefully that makes sense, I'm happy to attach the spreadsheet but can't work out how as per the instructions. I'll try in a subsequent post.