If you manage salespeople or other employees eligible for bonuses, calculating the bonus is often quite simple. For example, suppose employees get a bonus for sales over $100,000. If the employee sales are in column A, then you’d use formulas similar to the following:
=IF(A2 > 100000, “Bonus”, “”)
The formula returns the string Bonus if the sales figure goes over $100,000.
A slightly more complex case is when you want to calculate tiered bonuses for a sales team. Here’s an example:
- If the salesperson did not meet the sales target, no bonus is given.
- If the salesperson exceeded the sales target by less than 10 percent, a bonus of $1,000 is awarded.
- If the salesperson exceeded the sales target by 10 percent or more, a bonus of $10,000 is awarded.
Assuming the percentage over or under the sales target is in column D, here’s a formula that handles these rules:
=IF(D2 < 0, “”, IF(D2 < 0.1, 1000, 10000))
If the value in D2 is negative, nothing is returned; if the value in D2 is less than 10 percent, the formula returns 1000; if the value in D2 is greater than or equal to 10 percent, the forÂmula returns 10000. Figure 5.11 shows this formula in action.
Figure 1 This worksheet uses nested IF()functions to calculate a tiered bonus payment.
Technorati Tags: tiered bonuses, excel 2007
Popularity: 2% [?]
April 17th, 2009 at 8:47 am
My goal is to write a formula for a tiered bonus for salary. For example, a salary of $150,000 may be bonused as follows: 5% for first $25,000, then 10% for second $25,000, 15% for third $25,000, etc. Can anyone help me to write this? Thanks for any input. – JL