Excel 2016 The IF Function

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:33 Tips
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.

Cell references and much more are covered in Microsoft Excel 2016 In 90 Pages.