IF and IFS Functions: If You Want a Specific Result to a Logic Test

Locked lesson.

Upgrade

About this lesson

In this lesson, we learn the basic uses of IF and IFS and how to combine it with AND()/OR().

Exercise files

Download this lesson’s exercise file.

120 IF IFS.xlsx
25.2 KB

Quick reference

IF and IFS Functions

Learn the basic uses of IF and IFS and combine it with AND()/OR()

When to use

These functions are useful to provide a specified result if certain criteria are met and an alternative if the criteria are not met.

Instructions

  • The IF() syntax is: =if(test, what to do if true, what to do if false)
  • The test can be any logic test, including AND() / OR()
  • What to do if true/false can be
    • Simple: return text value or nothing (“”)
    • More complex: perform a calculation and provide the answer
  • Nested if occur if you add an if inside an if, but is not a very elegant way to writing formulas. Try an AND() or an OR() or try the IFS()
  • IFS() syntax is: =IFS(Test1, what to do if true1, test2, what to do if true2, test3, ….)
  • The IFS work well for performing multiple tests with different outcomes and the AND() and OR() works well if different tests can result in one of two outcomes
Login to download
  • 00:04 An IF function can be found with other logical functions on
  • 00:09 the Formulas tab in the function library, under Logical.
  • 00:14 A logical test alone will return a true or false AND functions and
  • 00:19 OR functions will return true and false values.
  • 00:23 But the IF function can return a calculation on another piece of text, or
  • 00:28 just a number as a result instead of just a true or false.
  • 00:32 In this video, I'd like to focus on the IF and
  • 00:35 show examples of what it can test and what it can return.
  • 00:40 We have an extract of the old warehouse sales example,
  • 00:44 here we're going to add some examples of the IF function.
  • 00:48 If you have Excel 2016 or later, you can use the IFS plural,
  • 00:52 and that largely replaces the need for nested IF formulas.
  • 00:57 And what is a nested IF formula?
  • 00:59 It's an IF inside of an IF, and we're going to use one in the state code
  • 01:04 column a little later, but first let's start with large sales.
  • 01:09 Our IF function in large sales will test a number and if it's big enough,
  • 01:14 we're going to display a piece of text as the output.
  • 01:18 If the sales transaction exceeds a value,
  • 01:21 lock that reference with dollar sign, so that we can copy this.
  • 01:26 If it's bigger than that value,
  • 01:29 display the words large sale, otherwise display nothing.
  • 01:35 You'll see that the last two arguments of the IF are optional,
  • 01:39 because they're shown in square brackets.
  • 01:41 But in this case, if I don't tell the IF function what to display if it's true or
  • 01:46 if it's false.
  • 01:48 This IF statement will basically work like a logical test, and
  • 01:52 it will return true IF true and false IF false.
  • 01:56 So I want to specifically return a value in the case of true and
  • 02:00 in the case of false, so you have to use the optional arguments here.
  • 02:06 I'm returning text in this example, and if it doesn't meet the criteria,
  • 02:10 I want nothing to be displayed.
  • 02:13 I'll type in two quotation marks in the case of false, and there we go.
  • 02:17 Copy that down by double clicking on the fill handle, and
  • 02:21 I have my large sales indicated.
  • 02:23 For the next formula, we're going to calculate the sales tax.
  • 02:27 Now sales tax for the purposes of this example, Oregon imposes no sales tax but
  • 02:33 the tax rate in California and Washington is 7.5%, as you can see in cell I6.
  • 02:40 To compute the sales tax then, we can use an IF function to test whether or
  • 02:45 not the state is Oregon.
  • 02:47 So, let's set that in.
  • 02:48 If the state in the location column is equal to Oregon,
  • 02:52 then the value IF true is 0, no sales tax.
  • 02:56 Otherwise, the value if false, if the state is not Oregon,
  • 03:01 we know the tax rate will be whatever it is and the cell I6 above.
  • 03:06 To compute the tax amount, we multiply the total sale in the column before by that
  • 03:11 rate above, and then we lock that reference to the rate,
  • 03:14 since we're going to need that throughout the whole list.
  • 03:19 And that will return the sales tax amount, close the parentheses and hit Enter.
  • 03:25 We copy this down and my tax rate for the whole list is calculated,
  • 03:30 zero for any cases of Oregon, 7.5% for all the others.
  • 03:37 The next formula we're going to create is for the state code, I know that we have
  • 03:42 three states in our entire list California, Washington, and Oregon.
  • 03:46 I want to show CA for California, WA for Washington, and OR for Oregon.
  • 03:53 I need three possible outputs but your normal IF statement can only give you two.
  • 03:59 One way we can make this happen is with a nested IF function inside of another IF.
  • 04:04 We can start by testing if the location cell is California, and if so return CA.
  • 04:11 But if the value is false, return the value produced by another nested IF
  • 04:15 statement, which we'll type in here.
  • 04:18 If B9 is Washington return WA, and in this case,
  • 04:22 we know if it's not California, and if it's not Washington by
  • 04:27 process of elimination, the state is Oregon, so return OR.
  • 04:32 In these types of complex formulas, watch out for your parentheses,
  • 04:37 Excel does color code them for you.
  • 04:40 The last parentheses that I've added has a match over here in red.
  • 04:45 But the first if parentheses does not have a matching parentheses yet, so
  • 04:49 we need to add one.
  • 04:52 Now I can copy the formula down and I have all my state codes, so,
  • 04:57 technically that does work.
  • 05:00 But I know that nested functions can be a bit confusing.
  • 05:03 The good news is, if you have Excel 2016 or
  • 05:07 later that formula we just made can be replaced by IFS,
  • 05:12 plural IFS, open parentheses.
  • 05:15 In the case of IFS, you can enter an argument test and
  • 05:19 the value to return if it's true, and then you repeat that process.
  • 05:24 My first logic test is B9 = "California","CA" but
  • 05:29 instead of adding a value to display a false, I just add another argument.
  • 05:35 If B9 = "Washington","WA".
  • 05:40 Now, we can do a third argument, IF B9 = "Oregon"," OR".
  • 05:47 And then we have the three different options, I can copy this down and
  • 05:51 it produces the same results as the nested if statements we did, but
  • 05:55 you can see the IFS is a little bit more straightforward.
  • 05:59 So that's easy enough, I've got some extra exercises lined up for you.
  • 06:04 There's an IF that requires two logic tests, and
  • 06:08 the output requires only two options where you have to use an AND
  • 06:12 and an OR to solve the problem, so good luck.

Lesson notes are only available for subscribers.

Gift this course