How to Calculate a Bonus in Excel With IF

To calculate a bonus in Excel, you turn the bonus rule into an IF formula: if the employee meets the condition, return the bonus amount, otherwise return 0. Once you know that pattern, every bonus plan is a variation of it: a percentage of salary, two conditions with AND, tiers with IFS, or a share of sales above target. This guide builds each one on the same small team, with results you can check.

The sample data#

All the formulas below use this table in A1:D6. Rating is a score from 1 to 5, and Sales is the yearly sales figure.

Type each formula in row 2, then fill it down to row 6 (Windows: Ctrl+D; Mac: ⌘D).

How to calculate a bonus with IF#

IF needs three parts: a test, the value if the test is true, and the value if it is false.

=IF(logical_test, value_if_true, value_if_false)

Rule: pay 10% of salary to anyone with a rating of 4 or more.

=IF(C2>=4,B2*10%,0)

• C2>=4 is the test.
• B2*10% is the bonus when the test is true. 10% is the same as 0.1.
• 0 is the result when it is false.

The results are Ana 5,200, Ben 0, Chen 6,100, Dana 4,500 and Eli 0. For a flat bonus, put the amount in the middle: =IF(C2>=4,1000,0) pays 1,000 to the three people rated 4 or 5. The IF function guide explains each argument, and the IF lesson lets you practise the pattern.

How to calculate a bonus with two conditions (AND)#

Use AND inside IF when the employee must meet every condition. AND returns TRUE only if all its tests are true.

Rule: 10% of salary, but only for a rating of 4 or more and sales of at least 100,000.

=IF(AND(C2>=4,D2>=100000),B2*10%,0)

Chen has a good rating but missed the sales figure, so he gets nothing. The AND function guide and the article on IF with AND cover more examples.

How to pay a bonus when any condition is met (OR)#

Use OR when one condition is enough. OR returns TRUE if at least one test is true.

Rule: a flat 1,000 for anyone rated 4 or more or with sales of at least 110,000.

=IF(OR(C2>=4,D2>=110000),1000,0)

Ana, Chen and Dana each get 1,000. Ben (rating 3, sales 105,000) and Eli miss both. The AND, OR and NOT lesson practises both functions, and the guide to IF with multiple conditions shows how to mix them.

How to calculate a tiered bonus with IFS#

Tiers give a different rate at each level. IFS checks its conditions in order and returns the result for the first one that is true, so always start with the highest tier.

Rule: rating 5 earns 15%, rating 4 earns 10%, rating 3 earns 5%, anything lower earns 0.

=B2*IFS(C2>=5,15%,C2>=4,10%,C2>=3,5%,TRUE,0)

The final TRUE,0 is the catch-all: if no tier matches, the rate is 0. Without it, IFS returns #N/A for Eli. The results are Ana 7,800, Ben 2,400, Chen 6,100, Dana 4,500 and Eli 0.

IFS needs Excel 2019, Excel 2021 or Microsoft 365. In older versions, nest IF functions instead:

=B2*IF(C2>=5,15%,IF(C2>=4,10%,IF(C2>=3,5%,0)))

It gives the same results. The IFS function guide and the nested IF and IFS lesson go further, and the articles on the IFS function and nested IF compare the two styles.

How to pay a bonus on sales above target#

Sales bonuses often pay a share of the amount above a target. Subtract the target, and use MAX to stop the result going below zero.

Rule: 5% of everything sold above 100,000.

=MAX(0,D2-100000)*5%

Without MAX, Chen's result would be -400, a negative bonus. The IF version does the same job: =IF(D2>100000,(D2-100000)*5%,0). For plans with several rate bands, see the sales commission calculator, which uses the same ideas.

How to add a cap, rates in cells and rounding#

Real bonus sheets need three finishing touches.

Cap the bonus#

Wrap the formula in MIN to set a maximum. With a cap of 6,000:

=MIN(6000,B2*IFS(C2>=5,15%,C2>=4,10%,C2>=3,5%,TRUE,0))

Ana's tier bonus of 7,800 becomes 6,000. Everyone else is under the cap and keeps their amount.

Keep rates and targets in cells#

Typing 10% or 100000 into every formula makes changes slow. Put them in input cells, such as the rate in G1 and the target in G2, and lock them with $:

=IF(AND(C2>=4,D2>=$G$2),B2*$G$1,0)

Press F4 (Mac: ⌘T) after clicking a reference to add the dollar signs. When the rate changes next year, you edit one cell.

Round the result#

Percentages can produce long decimals. Round the final bonus to cents with =ROUND(formula,2), or to whole units with =ROUND(formula,0), so totals match payroll.

Common mistakes when calculating bonuses#

These slips cause most wrong bonus figures:

1. Tiers in the wrong order. =IFS(C2>=3,5%,C2>=4,10%,...) gives everyone rated 3 or more only 5%, because the first true test wins.
2. Rates typed as whole numbers. B2*10 pays ten times the salary. Write 10% or 0.1.
3. Text instead of numbers. If a rating was imported as text, C2>=4 compares text and gives odd results. Check with =ISNUMBER(C2).
4. Quotes around numbers. C2>="4" compares against the text "4". Leave numbers unquoted.
5. No catch-all in IFS. Add TRUE,0 at the end, or anyone outside every tier gets #N/A.
6. Unlocked input cells. Without $, G1 becomes G2, G3 and so on as you fill down.

Practise it#

Try the exercise on this page: it is the AND rule from above, and you can check your answer against Ana's 5,200. Then practise IF in a live spreadsheet, where you type each formula and get instant feedback, and move on to the IF, AND and OR lesson to combine conditions with confidence.