Excel: Use IF to Calculate a Bonus

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem: My VP of Sales announced that we are paying a 2% bonus for all sales over $20,000 this month. How do I calculate the bonus?

Strategy: Use the IF function. The function has three arguments. The first argument is a logical test. This is any expression that will result in a value of TRUE or FALSE. For example, F2>20000 is a logical test. The next argument is a formula that should be used if the logical test is true. The final argument is a value or formula to be used when the logical test is not true. The formula =IF(F2>20000,0.02*F2,0) can be thought of in these words, "œIf the revenue in F2 is greater than 20,000 then 2% of F2, otherwise 0."

  1. An IF function calculates the bonus.

Additional Details: The formula will not pay a bonus for someone who sold exactly $20,000. If such a sale should get a bonus, then use =IF(F2>=20000,0.02*F2,0).