Excel Formulas & Functions Part 3: Conditional Statements | Chris Dutton | Skillshare

Excel Formulas & Functions Part 3: Conditional Statements

Chris Dutton, Founder, Excel Maven

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
8 Lessons (31m)
    • 1. Introduction To Logical Operators

      1:23
    • 2. Anatomy of the IF Statement

      4:01
    • 3. Nested IF Statements

      4:53
    • 4. AND/OR Operators

      8:47
    • 5. NOT vs. "<>"

      3:17
    • 6. Fixing Errors with IFERROR

      4:03
    • 7. Common IS Statements

      4:00
    • 8. Logical Operators Homework

      0:44

About This Class

This course is part 3 of a 9-part series on Excel Formulas & Functions: from basic to advanced.

Conditional functions and logical operators allow you to design complex formulas built on IF/THEN logic. In this section we’ll introduce each of the key conditional functions (IF, AND, OR, and NOT) and practice combining them with logical operators to help organize and categorize raw data.

About the Excel Formulas & Functions Series:

In this series, you will develop tools to transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool. Courses cover 75+ formulas, and feature hands-on, contextual demos and practice exercises designed to help you not only memorize formula syntax, but to think like Excel.

You'll learn how to write complex, powerful functions from scratch, allowing you to:

  • Build dynamic tools & dashboards to filter, display and analyze your data
  • Join datasets from multiple sources in seconds with LOOKUP, INDEX & MATCH functions
  • Pull real-time data from APIs directly into Excel using WEBSERVICE & FILTERXML
  • Manipulate dates, times, text, and arrays with ease
  • Automate tedious and time-consuming analytics tasks (no VBA required!)
  • And much more

If you're looking for the ONE series covering all of the advanced formulas and functions that you need to become an absolute Excel rock star, you've found it!

Transcripts

1. Introduction To Logical Operators: all right, first stuff. Congrats on finishing section two. I'm proud of you. High fives. Now let's move on to some bigger and better things. We talk about Section three, which is logical operators. And for those of you who don't know what logical operators are, they're basically founded in the simple concept of an if then statement. So if I'm hungry, then I'm going to go make a sandwich. That's a logical operator. So to start with the anatomy of the if statement what the formula syntax looks like, I'm gonna talk about nested logical operators so that you contest multiple logical statements in a single formula. We'll talk and and or operators two forms of the not statement, and then a handful of is statements. So there are whole slew of his statements in Excel. We're gonna talk about a number of them that I find most useful. Last but not least, two files for download got the PdF Excel for analysts, logical operators again. Please feel free to download and print that just to have them on hand as we moved to the section. And then we've got our Excel file that will be using four hands on demos called Section three Logical Operators, and that looks like this. Just a single tab called Weather Data. So go ahead and open that up, Open up the pdf and let's jump in. 2. Anatomy of the IF Statement: okay. Anatomy of the if statement, Uh, the if statement or syntax is relatively simple. You've got three components of the formula. Your logical test, your value of true and your value. If false, your logical test is basically any test that results in either true or false. So you could say test that the value of a one equals Google or that the numerical value and B two is less than 100. You could also incorporate more complicated formulas here. You could say, if the max value in column A is between 25 30 and so on so forth. It's literally an infinite number of tests you could put here and next. You've got value of true. This is what you want, excel to return or to do if your test proves true, so you could just put a number or text string here. You could also put a formula in which case Excel would evaluate that formula. If you're logical, test was true. And then, as you might expect, value of false does the same thing. It's what you want excel to do in the case that your logical test is false. So let's jump into excel and do a hands on demo open the section three logical operators document and will work from there just to give you a quick background of what we're looking at. We've got weather data from a station at Boston Logan Airport. Don't pay too much attention because I I made up some of these numbers, but you get the idea. We've got latitude, longitude, date and month of temperature and precipitation in Fahrenheit and millimeters, respectively. And from that basic information, we're gonna be building out additional categorical variables like freeze climate precipitation type conditions, etcetera using logical operators in the rest of this section. For this lecture, we're gonna start with column I the freeze column and all we want to do here is say, um, let's create a flag or binary variable that equals yes, if the temperatures freezing. In other words, if it's below 32 F otherwise returned No. So I'm going to use an if statement to do this. We're gonna take it nice and slow for the first time. Basically, I'm just going to type equals if an open the parenthesis as you can see, uh, this little helper boxes popped up, you can see the three components that we just talked about and again. The first thing I need is theological test. So she's gonna arrow over to G two, which is where my temperature field lives. Take the less than 32. So that's my test Gonna hit comma now. And he didn't put the value of true Since I want text here, I need to surround it in quotes I'm just gonna take Yes, calm again Brings me to the value if false peace Same idea. Take no surrounded in quotes and close the parenthesis. So you'll note that I left my reference to G to completely relative as opposed to fixing it . And that's because when I apply this formula down, I want my reference to change from G two to G three, g four because I always wanted to refer to the proper temperature in the proper row. So, as you can see, double click the lower right corner of the formula sell and it is populated all the values down as far as there's data, which is exactly what I want. Um, now just to kind of spot check it. It's working properly as you can see we have some cases of missing data and any values. Don't worry, that was intentional. We'll deal with that momentarily. But based on the values that it do have, 37 degrees is no because it's above freezing 12 and 23 or both below freezing. It all looks good. So there you go basic example of using the if statement. 3. Nested IF Statements: all right, So realistically, your test may not be quite a simple and straightforward as the example we just walked through. In many cases, you have tests that include a number of different criteria and a number of different potential values. When that's the case, you're gonna want to use nested if statements. The good news is that Excel makes it really easy to do this. You can include multiple logical tests within a single formula. And if you know the if formula syntax, then you know how to use nested if statements. So let's look at an example before we dive into Excel. Basically, we have another logical test here, based on the temperature field in column B. But instead of just saying, Is the temperature less than 32 Yes or no? We're asking. Two questions were saying, Is it less than 40? If not, let's see if it's greater than 80. If not, you know, return this otherwise value of false error. So in this case, we have three potential results, as opposed to two in our first instance. So basically the syntax should look familiar. You start with your logical test. B two is less than 40 your value, if true. So in this case, if your test is true, if your temperature is less than 40 climate equals cold. Now here's the catch. When you get to the value if false peace, here's where you start your next if test Um and basically what that does is it tells excel All right, logically work your way from left to right through this formula. And continue until you either find a test that evaluates as true, or you get to the end where your value if false value lives. I'm so in this case, it saying all right is be to less than 40. Yes or no? If it is, we're gonna call it cold and stop there. If it's not, we're gonna move to our next test. Which is is it a temperature greater than 80? Yes or no? If it is called climate hot and stop there, otherwise move on. And in this case, you know, if you get to the end, if you have a temperature between 40 and 80 55 for instance, both of these are going to evaluate false and yourselves gonna return devalue it. False error are the value false value of mild. So let's dive into excel and get her hands dirty. We're gonna be working with column J climate, colon and basically I want to do exactly what I just described. So I want to create a nested if statement, um to categorize climate as cold if the temperature is less than 40 as hot. If the temperatures greater than 80 and mild. Otherwise so start with my equal sign. If open the princess logical test just like before G two is less than 40. Value of true is going to be cold now. Value of false rather than just saying hot or other here, I'm going to start another if statement if the temperatures greater than 80 value of true hot in quotes because it's a text string that I want to return and then I get to my final value of false. So if I'm done incorporating all of my tests here, and I'm ready to kind of populate my catch all value of false message or value, I'm just gonna enter that here so it's gonna be mild. And now here's a really important note. When I add the next parenthesis, you'll see that It's orange and it matches with the orange parenthesis, which is part of my second if statement. What that's telling me is that I'm not done with this formula. I haven't closed out all of my pregnancies. So when I had the second, you'll see that it will be bold and black, and it matches with the first princess at the beginning of my formula, which tells me that I'm done and I can press enter eso again. I'm gonna leave the references relative so that when I populate this formula down, my references will change from row to row. So let's see if this worked. Got a bunch of cold values tempest below 40 and all of those instances here's a mild value . 43. That's correct. You can see that this is ordered by month, where one is January, so I may have to scroll a bit for start getting into the, uh, hot values. So here's one in August, 85 degrees equals Hot 75 smiled so it looks like it's working properly. And there you go, basic example of using a nested if statement toe add a second criteria to my test 4. AND/OR Operators: all right, so by this point, you're probably master of the nested. If statements which is fantastic thing is those statements were still based on pretty simple. If this, then that statements. What if we need to create a logical test that's based on a number of different fields or variables, as opposed to just temperature? For instance, um, one great example of this is if we wanted to create a field called Precipitation type, um, and precipitation type can take a few different values. Let's say we want to categorize it as snow, rain or none. Um, if you just think about it for a minute. There's no way to define snow, rain or none based on just a single field, because it's a function of two things that temperature and the amount of precipitation. So if nothing's falling out of the sky, precipitation type will always be none. If precipitation is greater than zero, meaning something is coming out of the sky, we need to know if the temperature is above or below freezing toe. Understand whether that is rain coming down or snow coming down. So that's where the and and or statements come into play. It's when you need to include while double logical tests that are based on a number of different criteria or variables. So just continuing on with the precipitation type example, you'll see that the sin taxes a little bit funky. It can be a little tricky to get used to. From a readability standpoint, it might seem to make more sense to say if condition one and condition to then in this case excel. It kind of flips it, and it always starts with if and or if or or if not, um, and then lists your criteria within parentheses. So reading through this example here, um, this and statement. Basically, what we're saying is logical Test one is if these two things are true. So if and d two equals yes, which that's are freezing column, which tells me that the temperature is freezing, it's below 32 degrees and C two is great and zero, so precipitation is greater than zero, then precipitation type equals snow. Now, if you remember the nested ifs and tax instead of the value of false, we move right on into our second logical test, which is another If and statement this case were saying if and d two equals no. So it's not freezing temperatures above 32 degrees. And also precipitation is greater than zero, then precipitation type equals rain. Then you're always gonna wrap it up. But the end with your catchall value if false value in this case, it's just gonna be none. Um, so in order for this test to be true, both of these conditions need to, uh, be true because it's AnAnd statement on gun. If none of these air true, it's gonna return the final value, which is none. And now what if we wanted to create an additional field called conditions that are either equal to wet or dry on the conditions air just based on precipitation? So if precipitation is none, we know that the conditions are gonna be dry no matter what. If the precipitation type his reign, conditions are wet. But if the precipitation type is snow, the conditions will also be wet. So we could say if precipitation type is rain than wet. If precipitation type is snow, then wet otherwise dry and do it like a traditional nested if or we can get a little bit more elegant and introducing or statement to do the same thing. So in this or statement were saying if or and then listing are two conditions. Basically, this is saying if either one of these is true, then we're going to return the Valley of True, which is wet. So if the precipitation type equals rain or ID equals snow, then conditions are wet. And then again, your catch all value of false is going to be dry, so it will return those two values. Quick tip. Here, Um, when you're writing nested functions, especially if you have a number of different logical tests all stacked together, you can just copy and paste pieces of your formula to save yourself some time. So rather than rewriting if and and if, or statements from scratch, just copy and paste and build your formulas that way. So let's bump over to excel and actually ah, walk through these examples hands on. So again, I'm gonna use the exact same conditions that I just laid out. So precipitation type is going to be the and statement that's based on freezing and precipitation, so columns I and H start with equals if parenthesis and parenthesis and then list my two logical is that must both be true. So freezing equals Start with the s and precipitation is greater than zero Close the parenthesis because that's my full and statement and then comment to my value of true. So if both of these are true, what's the deal? It's freezing their stuff falling from the sky. So the precipitation type is snow. Um, and now I'm going to start with my 2nd 1 again. I could go up here into the formula bar and copy this chunk and paste it. I'm gonna type this one out just to kind of walk you through it one more time and then later in the sections, we're gonna start to do some short cuts like that. So value of false. Remember, I have one more condition to test before I do my catch All value. So I'm gonna do another if and statement in this case, I to freezing field, uh, is going to be equal to know. So temperatures above 32 and precipitation is greater than zero. Close that out, comma over to value of true for this test. And that's gonna be called rain that my last comment gets me to my final value, if false. So it's none of these tests are true what I want to return and I just want none here. And then again, need to close it out with two parentheses. I can tell that I'm done because I got the bold black parenthesis closing out my entire formula. It enter, you know, just like before I use relative references so I could just double click and apply this down and it will update automatically. And then, you know one way to check my values. I can just look at the filter and see OK, all three of my potential values have populated none rain and snow. I'm also getting an error value because, as we talked about, there are some blank and missing values which will address momentarily. Now let's quickly do the conditions field. This is the or statement and again, the conditions that must be true are that either the precipitation type is snow or the precipitation type is reined, in which case I'm gonna call conditions wet, otherwise dry so equals if or two conditions R K two equals snow. Remember in quotes because I'm dealing with the text string comment to my second criteria, which is K two equals rain. Close out your statement. Calm over. If either of these air true, what value don't want to return, that value is wet. And then again, if none of those were true, my value of false is going to be dry. Put it in quotes, closing off it, enter and apply that down just like I did in the last example. And as you can see, I'm getting both dry and wet values. If I just do a quick spot, check any Time Column case. None. I'm seeing a dry value, and then snow equals wet. Rain equals wet. Everything looks good. So there you go. That's Ah, quick example of how to use and and or statements to develop more complicated and dynamic logical tests. 5. NOT vs. "<>": and in, or statements allow us to test whether Conditions A and B are either both true or if one or the other is true. But what if we want to test if a condition is not true? In that case, we have a tool called the Not statement or using the not equal to operator as a means of accomplishing that. So ah, here's an example where we want to redefine the conditions field, based just on the Precipitation column in column c. One way to do it would be to use the not statement and say, if not see two equals zero, which, in other words, is saying If C two is not equal to zero, then value of true is wet. Value of false is dry again. From a readability standpoint, it feels backwards. That's just the way Crazy Excel operates. So you just got to get used to it the second way to accomplish the same thing in what is, in my opinion, a more straightforward approach is to use the not equal to sign, which just looks like a less than followed immediately by a greater than symbol, so I could do the exact same thing by writing if C two is not equal to zero value of true wet value of false dry. And it's just a little bit cleaner sin taxes more straightforward, easy to comprehend. And you don't introduce these extra print disease that you would by using the not statement . So jumping over to excel Um, what I'm basically gonna do is revisit the conditions formula that we created in the last lecture using the or statement. And I'm just gonna basically delete that and start from scratch. And I'm going to use a not equal to, uh, test to accomplish the same thing on and again, I'm going to use the precipitation field as my my variable gonna type equals if and then arrow over to precipitation. So if it's not equal to zero, my value of true is going to be wet. So something's falling out of this guy. I don't know what doesn't really matter what otherwise conditions are dry. One Francis to close it. And there you go. So now what I'm gonna do is just apply this formula. It will overwrite all of my others, and because it's relative references, we'll update properly and as you can see it maintained All of the same value is the one thing that did change. Just gonna undo and redo to show you. Is this error value in Row 12 on? The reason that changed is because the or statement that we had previously written, remember was based on Precipitation Type Field, which had returned an error because that field in turn, was based on the temperature field, which has some corruptor missing data. In this case, we've redefined the formula purely based on column H, which doesn't seem to have the missing value. So the error disappears and it populates properly. So there you go. That's the not statement. 6. Fixing Errors with IFERROR: all right, so we talked about the affair. Statement briefly in lecture six. I think it's worth revisiting here because it's especially relevant in this context. Again, it's really an excellent tool to eliminate annoying error messages and replacing those with a value of your choosing. They're a couple implications of doing that. The first is in the context of just design and polish. It could be nice as a front and formatting tool or trick. It can also be really useful on the data side in the back end. So you may want to categorize your data consistently, whether you're seeing an error message or not, or if it's a numerical field that you're working with. Ah, and you want to be able to make statistical operations on that column of data. Sometimes having an any value in there will prevent you from properly calculating this values, so you may want to replace the N A with something like a zero or another value. So the syntax of the formula again is very simple. Just two pieces. The value, which is the formula or value itself that may or may not result in an error, and then the value if error This is what will replace the default error symbol. Um, with whatever you choose. So again, the tip here is just write your full formula first. And then at the end, your last step will be to wrap it in an if air statement. So let's jump to excel. And if you recall, with this weather data were dealing with, we've got some any values and our temperature column, which is throwing off some of the formulas that we built out in columns I, J and K. So, one by one, I'm just gonna wrap each of these in and if error and set a new value to replace the N A and we'll just apply it down and watch those error messages update. So, in this case, you know, I could assign either a no or a yes, in cases where I'm getting an error. But I don't want to, because I really don't know what the true underlying temperature should have been, So I don't want to just guess it it instead, I'm just gonna define anything with an error as other. So I'm gonna basically click right after my equal, sign them and sell. I to where my freeze formula lives. And I'm just gonna type if error open, a parenthesis. And then the value, as you can see, is my entire formula that had already written Jump to the end press comma. Now I'm at my value if error component and I'm just gonna type other quotes. Sorry is blocking you and close it off with one parenthesis hit. Enter. So now when I apply this down, it will override all my formulas. Add the affair earpiece. And if all goes well, replace any Anais or any other error messages with other boom. There you go. So an A in row 12 you can see now it says other I've got Let's see Anais in rose to 26 to 27. Those have updated to others. Well, looks good. So let's do the exact same thing for the Climate Column type. If error open the parenthesis jump to the end, comma other as a string. Close it off. Apply the formula down and there you go. So you might have noticed that column K originally had error messages. Now it doesn't, and that's just because it was based on the errors that it was seeing in I and J which have been corrected. So therefore the formula and Colin K. Is now good to go. So if we look at the filters, you can see the n a values disappeared. Now we just have yes, no other for climate. We have hot, cold, mild or other. So there you go using the affair statement to replace Anais with custom valleys. 7. Common IS Statements: All right, let's wrap up the logical operator section by talking about the is statement. It's nice and easy. Good simple one to finish on. So Excel offers a number of different is formulas. The point is that each checks whether a very particular or certain condition is true. Eso. When you type and is function, you're just gonna return either true or false. Those are the two possible values. Sin tax wise, it's about as easy as it possible gets. You just write the formula, open a parenthesis and drop in your value or reference and close it off and you're done. So here's a sample list. This is not comprehensive, but it should give you a sense of some of the statements that are available to you. You can check whether your reference cell or value is blank. You can check if it's a number versus text. If you're getting an error, you can check if your value is even versus odd. You can see if it's a logical operator or formula and so on and so forth. So let's jump into excel and we're gonna populate this last column column and called missing data. We want to identify and flag any cases where we have missing data in column G or temperature column. So I'm going to start with and his error statement so is error. Open parenthesis. Arrow over to G to close it off. Hit, enter. You see, I get false there because I'm getting a valid data point in G two. And then when I apply it down, you'll see it's flagged a true here and wrote 12 because I'm getting an error in G 12 so that seems to work nicely. But before I call it a day, you'll note that I also have missing values in column G. So it's not showing an error message, but it's still a missing data point that I need to account for. So if I wanted to just flag those, what I could do is change this from his error. Two is blank again. Just refer to G two. Apply that down. Now you'll see that Row six for have a blank flags. True, but the error in row 12 no longer flags. True because it's seeing an n a error, which is not blank. So what I need to do is combine these two and flag as true any case where column G has a blank or an error value, so I'll just keep the is blank that I already have. I'm just going to start with an or open up my parenthesis, which gives me my two logical values. Logical one is blank. Logical to is going to be is error. And just so you know, Aiken, I can either click on G to here to make that reference. I can also just type G two, and it will read it as a reference is, Well, just a little tip, um and then close it off. So now I have my or statement with two arguments. So if it's either blank or it's an error, this will flag is true, otherwise false. So let's see if that worked seems to have worked properly. I've got a blank and the true here, I've got an error and a true here. So it seems to be working properly. And now one other thing I can do here is used my filter and say, Show me the cases where I have missing data. So where this flag as true and I can see all right, what dates did those missing data points fall on? And then if I wanted to, I could cut that data out, um, or manipulated in some other way. But there you go. Those are just two examples of his statements. Again, there are a number of others, but with that that wraps up, logical operators get excited because Section four, we're gonna talk about statistical functions. 8. Logical Operators Homework: All right. Nice work. You made it through section three. Now it's time for some homework. Go ahead and open up the homework exercises. File again. It's available in the course Resource is or in electric to and jump to the second tab. A logical operators tab here. We've got a really simple data set with some student names. Got genders and test scores and columns. Seat. Now I have some instructions with four different steps. And through these steps you're going to use logical operators and conditional statements here if your annual or to populate some additional fields and columns D through G. Now remember, if you get stuck, the answers file is also available in the course. Resource is otherwise. Just drop me a line and I'll be happy to help me out. Good luck.