Are you trying to create a formula that performs different actions depending on the value in a cell? Then you’re in need of the IF function. To help you understand the IF function, nested IF functions, and using other functions in an IF, Belleyre Books has published a new video.
0:03 Excel 2016 The If Function
0:07 From the book Microsoft Excel 2016 In 90 Pages by Beth Brown
0:13 In this video you will learn
0:14 How to the use the IF function to perform an action based on the outcome of a logical test.
0:21 How to use a function as an argument in an IF.
0:28 How to create a formula with nested IF functions.
0:35 In this spreadsheet, I have test scores and averages.
0:40 I will create three formulas that use the IF functions to complete the Status, Grade, and Notes columns.
0:52 For the Status column, I want to create a formula that displays Passing if the Average is 60 or above. For averages below 60, I want to display Failing.
1:03 Since the IF function performs an action based on the outcome of a logical comparison, we can use it to compare the Average value to 60 and then display the appropriate message.
1:13 To do this, I enter a formula that uses the IFfunction in cell F3.
1:18 As you type, Excel displays an Intellisense function guide to remind you of the arguments needed.
1:32 In this case, the logical test is E3>=60
1:37 We type a comma
1:39 and then we enter the action to take when the logical test is true, which in this case is to display Passing.
1:48 We type a comma and then enter the outcome when the test is FALSE
1:52 which is to display Failing
1:54 Be sure to type the close parenthesis to complete the function.
2:00 As you can see, 80.3 is greater than 60 so Passing is displayed.
2:04 Now, let’s copy the formula to the rest of the column.
2:10 Failing is displayed when the average is less than 60.
2:15 To determine the letter grade associated with an Average, we will need to make several comparisons.
2:22 To do this, a formula that uses nested IF functions can be used.
2:33 In cell G3 we’re going to compare the Average to the score needed for an A
2:40so when E3>=90 we want to display an A
2:46 However, when that isn’t true we have several alternatives, so we’ll use another IF function to make a comparison.
2:59 So if E3>=80 we want to display a B
3:01 And here again, when that isn’t true we still have several comparisons to make.
3:07 So we’ll use another IFfunction
3:13 If E3>=70, we’ll display a C
3:23 And if that isn’t true, we’ll make a comparison to see if E3>=60
3:32 that requires us to display a D and that leaves us with just one last outcome
3:39 to display an F. Now we need to type all the closing parens.
3:43 As you can see Excel helps you out by highlighting the matching parens.
3:53 Let’s fill down. The correct letter grade has been calculated for each class.
4:01 As you can see, the failing letter grade compared all the values until it came to the very last outcome.
4:11 In the Note column, I want to display one message when the letter grade is an A or B, and a different message for grades below a B.
4:19 For this comparison, we’ll use the OR function.
4:22 The OR function returns TRUE when one or more of its arguments are TRUE.
4:42 So if the value in G3 is equal to an A
4:47 or when G3 is equal to a B
4:52 we want a TRUE outcome
4:56 in which case we will display “Great job!”
5:05 When we don’t have a TRUE outcome we want to display “Study more.”
5:21 And there you have it. By using the IF, nested IFs, and functions as arguments, you can create logical formulas in your spreadsheet.
5:34 Use the IF function when you want to perform an action based on the logical outcome of a comparison.
5:42 Use nested IF functions when you need to make more than one comparison.
5:49 You can use other functions as arguments in an IF.
6:00 All this information and more can be found in Microsoft Excel 2016 In 90 Pages by Beth Brown available at Amazon.com.