This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Use IF to Calculate a Bonus
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. A logical test 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 value or 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."
Figure 389 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).