4.6 Nested IF Function(s) Module 4 Section 4.6
Let’s look at using the IF function outside of the Insert Function feature. In the data file FUNCTIONS.xlsx, select the tab, NESTED IF FUNCTIONS. We will be nesting the IF function to provide more conditions.
Practice Exercise 9: Create an IF formula using nested IF functions to create additional conditions
STEP 1: We will be using a formula in cell address G2 that will incorporate our nested IF functions. This nested IF function assigns a Performance Bonus to employees based on the number of their SalesClassification. The higher numbers assign a bonus of different amounts depending on the number; the lower numbers (1 and 2) do not give the employee a bonus (FIGURE 1).
FIGURE 1
STEP 2: In cell address G2, enter: =IF(
 FIGURE 2
|
|
logical_test: F2=3
[value_if_true]: 2000 logical_test: F2=4 [value_if_true]: 5000 logical_test: F2=5 [value_if_true]: 10000 [value_if_false]: No Bonus |
STEP 3A: Enter the following in cell address G2 and press the Enter key:
=IF(F2=3,2000,IF(F2=4,5000,IF(F2=5,10000,”No Bonus”)))
Note: PAY PARTICULAR ATTENTION TO YOUR PARENTHESIS. YOU MUST HAVE THE SAME NUMBER OF OPEN AND CLOSED PARENTHESIS.
=IF(F2=3,2000,IF(F2=4,5000,IF(F2=5,10000,”No Bonus”)))
Note: PAY PARTICULAR ATTENTION TO YOUR PARENTHESIS. YOU MUST HAVE THE SAME NUMBER OF OPEN AND CLOSED PARENTHESIS.
STEP 3B: The logic of the above nested IF function follows:
- Excel looks at the condition you are testing: F2=3. If F2 is a 3, then the Performance Bonus will be 2000, since that number follows the F2=3. If F2 is not 3, Excel continues reading to the right.
- Excel looks at the condition you are testing: F2=4. If F2 is a 4, then the Performance Bonus will be 5000, since that number follows the F2=4. If F2 is not 4, Excel continues reading to the right.
- Excel looks at the condition you are testing: F2=5. If F2 is a 5, then the Performance Bonus will be 10000, since that number follows the F2=5. If F2 is not 5, Excel continues reading to the right.
- Excel has gone through all of the TRUE conditions. The only thing Excel can do is follow the next portion to the right, “No Bonus” which is the FALSE condition.
STEP 4A: 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 3
|
|
STEP 4B: All of the cells in Column G below cell address G2 will populate with various outcomes.
STEP 4C: 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. |
 FIGURE 4
|
END OF PRACTICE EXERCISE 9