Logic Functions: Using Logic to Supply TRUE or FALSE as a Result

Quick reference

• 00:05 Sometimes in Excel, you just need to test a cell to see if it meets
• 00:09 certain criteria, and the only output you might need is true or false.
• 00:14 If that's so, you would use a logic function.
• 00:18 This is how they work.
• 00:19 First, I'll show you logic functions for text, for date, and for number.
• 00:26 A logic function for text would be if I tested the location to be Oregon.
• 00:32 And this is what it looks like, equals the cell I want to test and
• 00:38 then I type in an equal sign and a quotation mark.
• 00:42 Type in Oregon, close the quotation mark.
• 00:46 So it's going to test if B8 is equal to Oregon.
• 00:50 And since the first one here is California, it's not equal to Oregon,
• 00:54 we expect a false.
• 00:56 And when we hit Enter, we get a false.
• 00:59 Copy the formula down and I can see that there are some trues in this column.
• 01:03 Wherever it says Oregon, it'll say true as a result.
• 01:09 Now let's use a logic test to see if a date is in May or not.
• 01:14 We have June transactions and we have May transactions.
• 01:17 So how do I test if the date is in May?
• 01:20 I can use a month function, month of that date equals May or
• 01:25 the number five for the fifth month.
• 01:29 For the first date, we see it's false because the date is June 28.
• 01:33 Double click on the fill handle and we can see everything after the first three rows
• 01:38 are May transactions so they are all true.
• 01:42 Now when testing a number, I can use any one of these operators up here.
• 01:47 Greater than, less than, not equal to, equal to, greater than or
• 01:52 equal to, less than or equal to.
• 01:56 Let's say we want to see if sales are greater than 700.
• 02:00 We type in an equal sign, click on the sales, amount greater than 700.
• 02:06 And we don't need a dollar sign, that's just a formatting symbol.
• 02:10 Hit Enter and the first one's true.
• 02:12 Copy it down and some of our values are true, others are false.
• 02:17 Now that we've seen logical tests for text, dates, and
• 02:20 numbers, let's try something a little more complex.
• 02:23 Let's try a logical test with an or condition.
• 02:28 What we want to know here is if the state is either Washington or
• 02:32 Oregon, give me a true if it's one or the other.
• 02:37 There's a nice or function in excel that has many arguments or tests, you can
• 02:43 actually test up to 255 arguments, which is just a general limitation in Excel.
• 02:49 But the point here is if any one of those arguments is true, the or
• 02:53 will return a true value.
• 02:56 Only one condition needs to be true with an or.
• 03:00 So what is our first argument?
• 03:02 Location equals Oregon, just like we did with the first test.
• 03:07 And the second test would compare the same location cell to Washington.
• 03:13 If it's either Oregon or Washington, or will return a true.
• 03:19 And the first one returns a false because in this case it's neither,
• 03:23 it's California.
• 03:25 Copy it down and we see the second cell is Washington, so
• 03:29 that's true, as are a few others when we see Washington or Oregon.
• 03:35 Let's try something else.
• 03:36 What if we want to see if a transaction is in Oregon and
• 03:41 it's a big sale like greater than $700.
• 03:44 We can use the and function.
• 03:48 Let's type in and, in our first argument we'll test our location.
• 03:55 Is this cell equal to Oregon?
• 03:58 The second argument is, are the sales in this cell greater than 700?
• 04:03 In an and function, all of the arguments must be true to result in a true value.
• 04:09 If any part of it is false, the whole thing is false.
• 04:13 And it has the same limitations as the or
• 04:15 function up to 255 arguments if you really need that many.
• 04:20 So the first large transactions in Oregon is this one
• 04:25 in row 14, and there are a few others on the list.
• 04:30 Now at this point, you might see where we're heading.
• 04:33 And this is to combine what we've done so far.
• 04:36 How do we test whether a transaction is in either Oregon or
• 04:41 Washington and is greater than 700?
• 04:45 We combine an or function and
• 04:49 an and function by nesting them.
• 04:53 The first argument for this example would be an and.
• 04:57 The sales have to be greater than 700 in either case.
• 05:02 Then our second argument will be the or function,
• 05:05 test if the location is either Oregon or Washington.
• 05:08 So let's type in our or function.
• 05:10 The first argument, is the state equal to Oregon or
• 05:17 is the state equal to Washington?
• 05:23 If either one of those is true, that part will return a true.
• 05:27 So let's close the parentheses for the or, close the parentheses for the and, and
• 05:32 before I press Enter, let's just review what we have here.
• 05:36 For this and function to be true, both arguments in here will need to be true.
• 05:41 The first argument tests, is the sales figure greater than 700?
• 05:45 If it's 701 or better, that is true.
• 05:49 The second argument tests, is the state Oregon or is it Washington?
• 05:54 If either one is true, that part is true.
• 05:58 And if both arguments are true, the whole thing is true.
• 06:03 If either one is false, the whole thing is false.
• 06:07 So the first one, it's in California, so the or argument results in a false,
• 06:12 which means the whole thing is false.
• 06:16 Let's see what the next one does.
• 06:18 We'll drag the fill handle down.
• 06:21 The next one is in Washington, so the or returns a true, but
• 06:25 denying the sales is very small.
• 06:28 So the first argument is a false.
• 06:31 So the and returns a false.
• 06:35 Let's copy this down all the way down the column and
• 06:39 see if we get any trues, and we do.
• 06:41 This one's in Washington.
• 06:43 So the or returns a true and the sales are over 1,000.
• 06:47 So that part returns a true as well.
• 06:49 So overall, that one is true.
• 06:53 The combination of the and and the or is quite handy,
• 06:56 especially if you're thinking about using some nested if functions.
• 07:02 Combining ands and ors can often make nested ifs unnecessary,
• 07:07 and make your formulas pretty simple too.
• 07:11 All right, in the next lesson, we'll talk more about ifs.

Lesson notes are only available for subscribers.