Use AND(), OR(), and NOT() in nested functions

- [Instructor] Three of the functions that you really need to be comfortable with before taking the exam are And, OR, and NOT. In this video, we'll learn how to use the three of these functions in nested functions. Now, remember that the And function, when used, requires that both sets of criteria are met. The OR function requires that one or both sets of criteria are met but at least one of them The NOT function tells us what is not equal to the logic or criteria that we enter. And when we create nested functions, a lot of times we'll use the IF statement to say if this and this is true then return this value. Let's take a look at our AND, OR, NOT workbook and you'll see that we have three questions that we should answer. I've made these a little cryptic because on the exam they are not going to tell you, "Use this function to get the following result." You'll have to read the question or just read the information that you've been given and determine which function you want to use. And just pay close attention to whether or not you're being asked to return a specific text result or, as in our third example, it'll just be a true or false statement. So let's start with our first one. Is the employee hourly or contract? And if it's true we're going to return yes, if it's not true we will return no so we're going to use a nested IF statement with the OR function. I'll start in H4 equals IF open our parentheses and type OR and open our parentheses again. So we're ready to put out logic in. Is the status equal to hourly? And be sure when your typing the text in you include it in double quotation marks and type it exactly the way it's entered into the cell. So is it hourly or, we'll put a comma in for our second statement, does C4 equal contract? Now we can close our double parentheses and now we let Excel know what we want to say if it's true. Type a comma, open your quotation marks. If it's true say yes, if it's not say no. And notice that those statements are also separated with a comma and enclosed in double quotes. Now I can close my parentheses and hit enter. So the first record does not meet that criteria. It is not hourly or contract. Let's copy the formula down and we see that our second employee is contract and so is the third so it does look like it's working correctly. Now I can double click on the auto-fill handle and it will copy that formula all the way down. Let's take a look at our second question. We're looking for people who will receive a salary increase in 2020. We're looking for people in the marketing department whose salary is less that $50,000 and if they meet both of those sets of criteria so it's going to be an AND statement, then we will insert "increase" into the cell otherwise it will be blank. Okay, equals, it's going to be an IF statement. It's an AND statement this time so if the department equals marketing comma and the salary is less than $50,000 close our parentheses and put a comma in and end quotation marks. I'll type the word increase otherwise we want nothing returned so we're going to put blank double quotes in so nothing in between those double quotes. Close our parentheses and hit enter. Okay, so the first employee does not meet both sets of criteria. Let's go ahead and copy this down and we see our second employee is in marketing and they do make less than $50,000 so they meet both sets of criteria and we see that "increase" has been inserted. Okay let's move over to our third example. We're going to use the NOT function. If the employee is not in manufacturing or product development, so here we have a nested NOT OR statement, we're just going to have it return true or false. Equals not or department does not equal manufacturing comma, department does not equal product development and let's close our parentheses and hit enter. So the first record was returned as true because it's true they don't work in either manufacturing or product development. Let's go ahead and copy this formula down, I'll just double-click on the auto-fill handle. Here's our first false statement because the person does work in product development and that's why false was returned. So these are three really good examples of how to use AND, OR and NOT in a nested function. I want you to practice using these three functions with your own data. That way on the exam if you come across a task that's related to AND, OR or NOT in a nested function, you'll be able to determine which function will work the best.