How to Use an Excel IF Function

Sometimes you need your data to tell you more about itself. The versatile IF function is how you can turn raw numbers into useful information.

For example: Your wholesale company charges different rates depending on the size of bulk orders – customers who order more receive volume discounts. You want a way to quickly calculate the volume discount on a batch of orders so your sales team can deliver the correct quotes to their customers. In a nutshell, you need to calculate your cost and volume numbers into a price for your customers based on an additional bit of logic.

Here is how your data might look in a simple spreadsheet:

To setup an IF function, try thinking about your problem in natural language:

Problem: IF(the number of units is over 1000, the customer receives a volume discount of 3%, otherwise the customer receives no discount).

Now, just fill in the cells and values for the statement:

• D2 is the cell that shows the number of units sold.
• 03 is the discount (3%) received if D2 is greater than 1000
• 0 is the discount received if D2 is not greater than 1000

If you are still confused about how to use an IF function, Excel offers some help:

1. Click on the Formulas tab, then click the Insert Function
2. Select IF from the Insert Function dialog box; click OK.
3. Fill in the function arguments in the next dialog box. The dialog box includes prompts to help you setup your IF function. Click OK. Excel will populate the cell with the function in the correct format.

Advanced:

Once you become comfortable with using the IF function, you may begin to build more complicated formulas. For example, Nested IF functions can 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
• 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 function 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 repetitive tasks.