Using the IF Function - Exercise

Contact Us or call 1-877-932-8228
Using the IF Function - Exercise

Using the IF Function

Duration: 15 to 25 minutes.

In this exercise, you will practice using the IF function.

  1. Open Functions.xlsx from your Excel2013.2/Exercises folder and go to the sheet named "IF".
  2. Use the IF function to enter "Yes" or "No" in column E based on whether revenue from each customer exceeded $10,000 (i.e., if revenue exceeded $10,000, enter "Yes"; otherwise, enter "No").
  3. Use the IF function to enter "Yes" or "No" in column F based on whether the number of purchases from each customer was greater than or equal to 20 (i.e., if # of purchases exceeded 19, enter "Yes"; otherwise, enter "No".)
  4. Use the IF function to enter the revenue received from customers located in Utica, and only Utica, in column G (i.e., if the customer is located in Utica, enter revenue; otherwise, leave blank).

Solution:

  1. Use the IF function to enter "Yes" or "No" in column E based on whether revenue from each customer exceeded $10,000.
    1. The information you need to enter this formula is:
      1. Logical Test: If revenue is greater than $10,000, then...
      2. Value if True: "Yes"
      3. Value if False: "No"
    2. The formula is: =IF(B2>10000,"Yes","No")
    3. Enter the formula using the Insert Function command:
      1. Select cell E2.
      2. On the FORMULAS tab, in the Function Library group, click the Insert Function command:Insert Function Command
      3. In the Insert Function dialog box:
        1. Search on "IF" or, in the Or select a category drop-down box, select Logical.
        2. Under Select a function, select IF.
        3. Click OK.OK Option
      4. In the Function Arguments dialog box, enter the following values and click OK:
        1. Logical_test: B2>10000
        2. Value_if_true: "Yes"
        3. Value_if_false: "No" Values Entered
      5. Copy the formula from cell E2 to cells E3:E8.
  2. Use the IF function to enter "Yes" or "No" in column F based on whether the number of purchases from each customer was greater than or equal to 20.
    1. The information you need to enter this formula is:
      1. Logical Test: If the number of purchases is greater than 19, then...
      2. Value if True: "Yes"
      3. Value if False: "No"
    2. The formula is: =IF(C2>19,"Yes","No")
    3. Enter the formula using the Insert Function command:
      1. Select cell F2.
      2. On the FORMULAS tab, in the Function Library group, click the Insert Function command:Insert Function Command
      3. In the Insert Function dialog box:
        1. Search on "IF" or, in the Or select a category drop-down box, select Logical.
        2. Under Select a function, select IF.
        3. Click OK.OK Option
      4. In the Function Arguments dialog box, enter the following values and click OK:
        1. Logical_test: C2>19
        2. Value_if_true: "Yes"
        3. Value_if_false: "No"
        Entered Values
      5. Copy the formula from cell F2 to cells F3:F8.
  3. Use the IF function to enter the revenue received from customers located in Utica, and only Utica, in column G.
    1. The information you need to enter this formula is:
      1. Logical Test: If city is Utica, then...
      2. Value if True: Revenue (B2)
      3. Value if False: None ("")
    2. The formula is: =IF(D2="Utica",B2,"")
    3. Enter the formula using the Insert Function command:
      1. Select cell G2.
      2. On the FORMULAS tab, in the Function Library group, click the Insert Function command:Insert Function Command
      3. In the Insert Function dialog box:
        1. Search on "IF" or, in the Or select a category drop-down box, select Logical.
        2. Under Select a function, select IF.
        3. Click OK.OK Option
      4. In the Function Arguments dialog box, enter the following values and click OK:
        1. Logical_test: D2="Utica"
        2. Value_if_true: B2
        3. Value_if_false: ""
        Entered Values
      5. Copy the formula from cell G2 to cells G3:G8.
Next