This screencast is going to cover the three logical functions IF, AND and OR. I've got this spreadsheet that you can work along with me if you'd like. So the first thing I'm going to talk about is an IF function. The IF function in Excel basically test to see if a condition is true and then it can do one of two things, if that condition is true it can do one thing if that condition is false it can do another thing. I'm just going to start by typing in a quick example. So I type in IF, and then that's our logical test goes first. So what I'm going to do is I'm going to check to see if the cell next to it is equal to 3. So this first argument of the IF function has to be some sort of a conditional expression. Now, let me back up, let me just show you a couple of these conditional expressions so I could do something like 4 = 5 and that's going to be a false. I could do for 4 = 4 and it's going to be a true. So essentially with these conditional arguments were asking Excel IF that conditional is true or false, we can also do less than so is 4 less than 9, that's true. Equals 4 greater than 9 that's false. And so conditionals usually have equal or less than signs, less than, equal, greater than and so on. So let's go back here, I'm going to do IF, my conditional is going to be if the cell next to it is equal to 3. The second argument of the IF function is what do we want to be in that cell if the value is true, and I'm just going to say 50. So if we have a 3 where you're going to put in this cell a value of 50. Otherwise, we're just going to put in 0, all right? So obviously let's double-click in here again cell B4, which is a 1 is not equal to 3, so then we don't do the second argument because that's if it's true if that conditional is true, so we do if that conditional is false and we put a 0 in there. Now, if I drag this down same thing 2 is not equal to 3, so we're going to put a 0 in there. But the next one B3 is equal to 3, and so we're going to put a 50 because our conditional is true, all right? So then I can drag this down and that's how we can do a simple IF function. Now, we don't have to return values so I could do something like IF, if this I'm going to refer to that column B again equals 2. So if that's true can put strings in here with quotations, so I could say something like equal to 2. Otherwise, well, first of all, we don't need to have a third argument that's optional. But I could just do something like empty quotations, which would be nothing. Now, you notice if I leave off that third argument completely and I press Enter, it's going to give me a false. So if you want it to be empty, you have to put in a set of empty quotation marks. And then I can drag this down and when it finds a 2, it's going to say equal to 2, which is what we did there. Another example we can use this in an array formula. So if you've got an older version, you can highlight this type in =IF, and then we can do this entire array of once equals to 4, if that's true. I'm going to type in Yippee. If that's false, maybe I'll put in something like Bummer, all right? And then I can do Ctrl+Shift+Enter on the older versions or Enter on the newer versions, and we can do that all in one array formula. So let me talk about the AND and the OR functions now. The AND function and the OR functions have arguments and those have to be booleans, booleans are either true or false. Now, they can be just false or true the actual words in Excel or they can be 0s and 1s. 0s means false and 1s mean true. So the AND, let me just put in AND of x over here and y. An AND is only going to be true if both of the two arguments are true. So if they're if neither of them or either of them are true that's going to yield a false. They both have to be true in order for that to be true. So this is somewhat straightforward, we can do the same thing with numbers remember 0 and 1 correspond to false and true. So we can do the same thing and we get false, the AND function always gives true or false not the numbers. But we can use as operators as arguments 0 and 1. The OR function, the OR function means one or the other has to be true. So I can do OR again, there's two or more arguments. So I could actually have lots of these arguments in there and the same thing goes with the AND function, so I can do OR x, y, and I can drag that down. So if at least one of the arguments is true, then the or function is going to be true. And again, we can have multiple arguments other than two, I'm just going to copy this formula, the same thing goes with 0s and 1s, and that's how the OR and AND functions work. Let's go through an example here that's going to integrate some things that we just learned. So I've got a couple of people over here and maybe they have some sort of subtotal and we're going to calculate the tax, the discount and then the total that they have to pay. We are going to give Matt, for whatever reason he gets a 20% discount. So I've named the discount D-I-S-C up here in the name box. I've also named our tax rate T-A-X up here in the name box. So let's just put in after a discount. I'm going to use an IF, so we're going to say IF that person is equal to Matt, then if that's true our discount is going to be 20%, which is our discount of the subtotal. So I can multiply those two, if we haven't found Matt, if our person over here is not Matt, then we have a 0 discount, so I can press Enter. So you see Vanessa is not Matt. So Vanessa does not get a discount if I copy this down. You see that Matt gets a 20% discount, which is $12 and now we can compute the tax. So the tax is going to be the tax rate times the difference between the subtotal and we're going to subtract off the discount. So the tax for Vanessa is $4, pull this down, Matt, so after discount $48 is his subtotal, but he's only taxed $4.80, which is 10%. And then we can finally put the total on here, which is the subtotal plus the tax, and then I can copy this down. So that's an example of how we might use a selection strategy an IF statement to apply something like this. What if we go a step further and maybe Vanessa and Bob neither of them has to pay taxes? Maybe they're tax exempt for whatever reason. So let's go up here into the text formula and I'm going to put in an IF statement. Now, we're checking to see if the person is Vanessa or Bob, so we're going to put in an OR function and I'm going to say OR, so if B7 is equal to Vanessa or B7 is equal to Bob, all right? So that part the OR will give us a true if either of those is true if B7 is Vanessa or Bob. And then in the IF statement if that's true, then the tax is going to be 0 for Vanessa and Bob. And then if it's not, so if that's false, then the tax is going to be just as it was that we calculated earlier. And then I can close that with a right parenthesis. And you see that Vanessa is not charged any tax, I can drag this down because it's a new formula. We need to redrag, and you see that Vanessa and Bob neither of them is charged any text. And then finally we can just make sure that our total is correct. That's how we could integrate an OR statement into to the text column here. So hopefully you learned a little bit more about if and and or logical functions in Excel in this screencast. Thanks for watching.
