Preview
The IF Function
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.
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.
STEP 1: Open the data file, “Functions,xlsx” Exercise 7 tab. 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.
STEP3: Select the Insert Function. There are multiple ways to select it:
STEP 4: Option 1: Select the icon on the Formula Bar (FIGURE 4)
Option 2: Select the Formula tab. The Insert Function icon is on the far left of the toolbar (FIGURE 5).
STEP 5: Select the IF function from this list; you may have to scroll to view it (FIGURE 6).
STEP 6: If you don’t see it, you may select “Logical” from the “Or select a category” section of the dialog box.
STEP 7A: (FIGURE 7) The AVERAGE function is entered in the first argument (Logical_test) box. Enter the following argument into that box: AVERAGE(B2:B6)>15000
We will SUM data in the second argument (Value_if_true) box. Enter the following argument into that box: SUM(D2:D6)
STEP 7B: If the AVERAGE first argument is TRUE, then the SUM(D2:D6) function executes.
If the AVERAGE of cells B2:B6 is under 15000, then it is FALSE, and the result in cell C7 is 0.
STEP 7C: Select the OK button.
STEP 8: (FIGURE 8) 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
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: (FIGURE 9) Enter the following into cell address G2 and enter the following:
=IF(F2>3,10000,”No Bonus”)
STEP 3: After pressing the Enter key to execute the above nested function(s) in Step 2, go to the bottom-right corner of cell address G2. When you see a thin black cross, double-click the mouse (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.
END OF LESSON
Leave a Reply