Midlands Microsoft Office Provider

4.5 The IF Function – Intro to Excel 2016 Data Analysis

4.5 The IF Function ​(Module 4 Section 4.5)

The IF function is great to use when you only need one condition that needs to be met in order to get a TRUE result. The great thing about the IF function is that it gives you much latitude in what kind of  result occurs based on a TRUE result. You can also specify several results based on a FALSE result (FIGURE 1).

​A common use for the IF function is to expand the usefulness of other functions that perform logical tests. For example, the IF function performs a logical test and returns one value if the test evaluates to TRUE and another value if the test evaluates to FALSE. 

FIGURE 1
Practice Exercise 7: Concerning the IF function, we will use the data table below (FIGURE 2) to construct arguments for each employee. Use the Insert Function dialog box to construct each argument.
FIGURE 2
STEP 1: ​Select IF FUNCTION – EXERCISE 7 tab on the data file “FUNCTIONS.xlsx”. Make cell D7 the active cell.

STEP 2: ​Special Note: “Help on this function” is at bottom of Insert Function dialog box shown in Steps 6 and 7 (FIGURE 3).

When selected, “Help on this function” will provide meaningful information to help you better understand how the function works. You are shown examples of how the function works (including videos). It also shows some helpful links that also provide helpful information.

Picture

FIGURE 3
STEP 3: ​Select Insert Function. There are multiple ways to select it:

Option 1: Icon on the Formula Bar (FIGURE 4).
Option 1: Select Insert Function icon from Formula Bar

Picture

FIGURE 4
Option 2: From the Ribbon Function Library section (FIGURE 5).

Picture

FIGURE 5
STEP 4: ​From the “Or select a category” section, on the drop-down menu, select “Logical” (FIGURE 6). 

​STEP 5: The Logical category functions will appear including the IF function. Ensure IF is highlighted. If you do not see it, scroll down. Once visible, click it to highlight it.

Picture

FIGURE 6

​STEP 6A: In the “Logical_test” empty block, enter: “AVERAGE(B2:B6)>15000”
The AVERAGE function is the 1st argument. If it is TRUE, then the SUM(D2:D6) function executes (FIGURE 7).

STEP 6B:  In the “Value_if_true” empty block, enter: “SUM(D2:D6)”
​If the AVERAGE of cells B2:B6 is under 15000, then it is FALSE, and the result in cell D7 is 0.

STEP 6C: Select OK button

Picture

FIGURE 7
STEP 7: ​Since the 1st argument checked Average(B2:B6) to see if it is over 15000, and it is (cell B7), it is TRUE, therefore the TRUE output result is the SUM(D2:D6) shown in cell D7, which is $23,174.10 (FIGURE 8).

Picture

FIGURE 8
END OF PRACTICE EXERCISE 7
Practice Exercise 8:  Create an IF formula using the IF function in a table

STEP 1: In the data file FUNCTIONS.xlsx, select the tab, IF FUNCTION – EXERCISE 8. 

In this exercise, we will be assigning a Performance Bonus to employees with a Sales Classification of 4 or 5. If their classification is 1, 2, or 3, they do not receive a bonus.

STEP 2: Enter the following into cell address G2 and enter the following:
=IF(F2>3,10000,”No Bonus”) (FIGURE 9).
​

Picture

FIGURE 9
STEP 3: ​After pressing the Enter key to execute the above nested function(s) in Step 3, go to the bottom-right corner of cell address G2. When you see a thin black cross, double-click the mouse (FIGURE 10).

Picture

FIGURE 10
STEP 4A: All of the cells in Column G below cell address G2 will populate with various outcomes (FIGURE 11).

​STEP 4B: Check the bonus amounts assigned in Column G to the corresponding SalesClassification number in Column F to ensure that you performed the above steps correctly.

Picture

FIGURE 11
END OF PRACTICE EXERCISE 8