How to Use the IF Formula in Excel, Part 2

As amazing as the IF formula is alone, it really comes into its own when used in groups. In our last post, we talked about how to apply discounts to customer quotes based on a set of criteria being met. This week we’ll introduce you to nested IF functions. Nested IF formulas help you calculate more complex discounts, such as multiple rates for multiple tiers of purchasing.

Problem:

IF(the number of units is over 1000, check to see IF(the number is over 5000, give the customer a 6% discount, otherwise give the customer a 3% discount), if the number of units is not over 1000 the customer receives no discount).

=IF(D2>1000, IF(D2>5000, 0.06, 0.03), 0)

• D2 is the cell that shows the number of units sold.
• If D2 is greater than 1000, the formula checks to see if D2 is also greater than 5000
• 0.06 is the discount (6%) received if D2 is greater than 5000, otherwise, 0.03 is the discount (3%) received for D2 that exceeds 1000
• 0 is the discount received if D2 is not greater than 1000

The IF formula gives you a powerful tool for adding true/false logic to your data that will help speed decision-making (such as discounts!) and save you time on otherwise repetitive tasks.