Maintenance Alert: Saturday, December 9th, 7:00am-5:00pm CT. During this time, the shopping cart and information requests will be unavailable.

How to Use the IF Formula in Excel, Part 1

Sometimes you need your data to tell you more. The IF formula as a function gives your data a voice and turns 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 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:

Fred Pryor Seminars_Excel if formula figure 1 sample sales data

To set up 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:
Fred Pryor Seminars_Excel if formula figure 2 volume discount if function

=IF(D2>1000, 0.03,0)

• D2 is the cell that shows the number of units sold
• 0.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 button.
Fred Pryor Seminars_Excel if formula figure 3 MS Excel 2010 for Windows

2. Select IF from the Insert Function dialog box. Click OK.

Fred Pryor Seminars_Excel if formula figure 4 insert function

3. Fill in the function arguments in the next dialog box. The dialog box includes prompts to help you set up your
IF function. Click OK. Excel will populate the cell with the function in the correct format.

Fred Pryor Seminars_Excel if formula figure 5 Function Arguments

PRYOR+ 7-DAYS OF FREE TRAINING


Courses in Customer Service, Excel, HR, Leadership, OSHA and more. No credit card. No commitment. Individuals and teams.


Choose a Seminar and Save $10:

Related Excel® Articles

PRYOR+ 7-DAYS OF FREE TRAINING


Courses in Customer Service, Excel, HR, Leadership, OSHA and more. No credit card. No commitment. Individuals and teams.

Categories

5 Comments

  • DAISY says:

    HOW TO USE THE APPROPRIATE FUNCTION TO COMPUTE A DISCOUNT AS FOLLOWS:
    5% MULTIPLIED BY THE FEE IF THE BIRTHDATE IS BEFORE JULY 1,2009, OTHERWISE NO DISCOUNT.

    • Excel Tips and Tricks from Pryor.com says:

      A 5% discount can be calculated at 95% of a fee (100% – 5% is 95%). We want to pose the question “IF the birthdate is less than July 7th, 2000.”
      The IF() statement below will do just that. The DATE(2009,7,1) within the IF() is the comparison date.
      =IF(A2<DATE(2009,7,1),B2*0.95,B2)
      Reads like this:
      “If the date in A2 is less than July 7th, 2000 then multiply the fee (B2 by 95%), or else, use the entire fee in B2)”
      As you can see – that function can copy down to the next row and derive a different, correct, answer.

      Note* The IF() function is very versatile and worth your study. The comas separate the condition, from the TRUE and FALSE responses.

  • Shelbi says:

    How should you use it if the problem is :

    “if the payment method was express miles or rewards, the customer should receive the discount (20%). otherwise, no discount”

    • Excel Tips and Tricks from Pryor.com says:

      The word “or” in your question will prove to be the sticky part but this can still be done.
      We must use two IF() functions – one nested within another.

      In English, the nested IF(), above would read:
      “If cell A2 is ‘EXPRESS MILES’, then the answer is 0.2 or else, if cell A2 is ‘REWARDS’, then the answer is 0.2 or else, the answer is 0.”
      0.2 is, of course, 20%

      Because they are nested, the IF()s are evaluated one at a time, in sequence, from left to right.
      If both are false (neither ‘EXPRESS MILES’ nor ‘REWARDS’ is true) then zero is the answer for that cell.

1 Trackback