Re: IF Function to calculate bonus based on tiered bonus rates

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.

See attached (hopefully)

Originally Posted by PaulM100

Check the yellow banner above

Thank you, I've worked it out now!

Hi Geoff,

Thanks ever so much for taking a look.

When I paste the formula you have given into my workbook I'm getting a #VALUE! error for all the fee income figures above $150,000.

Upon trying to diagnose it seems it may perhaps be a formatting issue as it is trying to compute $150,000 - 140,000.

Not sure if this would cause an issue, apologies as you may have gathered I'm very inexperienced with Excel!

Apologies Geoff, I've just worked out that I typed the dollar on some cells sign instead of using formatting, now seems to work great. Thank you so much!