Use AND(), OR(), and NOT() in nested functions - Microsoft Excel Tutorial

- [Instructor] Function statements such as AND, OR, and NOT can be used by themselves or they can be combined to make a logical determination if the values meet both criteria; One criteria or none of the conditions. After watching this video, you'll be able to create logical functions using AND, OR, and NOT. Let's jump right into our document. We're in the O4_01 AND_OR_NOT workbook. And we have three questions that we need to answer. The first is, does the department equal marketing and the number of years with the company? Is it greater than or equal to five? I'll click in H four, and type in equals AND, open my parentheses. And we want to know if B four equals "marketing." And I'll put "marketing" in quotation marks, comma. And is E four greater than or equal to five? Close parenthesis and hit Enter. So these functions used by themselves will return TRUE or FALSE, letting us know whether it meets the criteria. So it's not the marketing department, nor have they been with the company for greater than or equal to five years. So that's false. Let's use our AutoFill handle to copy this down. And let's go back up to the top. And look at our second row of entries and we see it's true because they are in the marketing department and they have been with the company for more than five years. So that meets both sets of criteria and that is why TRUE was returned. Now, let's use AND and OR together in our next column. We want to know if the department equals training and it meets one of these sets of criteria. Is the status hourly or contract? We'll begin by typing in equals AND, open our parentheses. And first, we want to know is B four equal to training? We'll put a comma in, or this is where we nest our OR statement. Does C four equal hourly, or does C four equal contract? Close our parentheses. So we're looking to see if B four equals training. And then if so, does the status equal hourly or contract? And if it meets either set of criteria, it will return TRUE. Let's hit Enter. And it says, I'm missing my closing parentheses. I do want to accept this correction, so I will click yes. So I should've put that closing parentheses and neglected to do so, but luckily Excel caught that for me. So the first one is false. They're not in the training department. So we don't even need to look at the second set of criteria. Let's double-click on the AutoFill handle. Here's our first instance of TRUE. They are in training and they're contracts. So it did meet one of the OR statement criteria. Now, let's look at the NOT statement. We want to know if the department is not sales, equals NOT, open parenthesis, B four, equals sales, close quotes, close parenthesis, hit Enter. So our first entry is not in the sales department. So it's true. I'll double-click the AutoFill handle. And here's our first false statement because they are in sales. So that's how you can use AND, AND and OR together, and NOT. Keep in mind, this will only return a TRUE or a FALSE statement. And we'll learn in the next video how to nest this with IF so that we can tell Excel what we want returned in the cell if it meets the criteria.