Locked lesson.
About this lesson
We'll look at logic functions, including: >, <, =, <>, AND and OR. We also test single or double criteria, and illustrate the MONTH function.
Quick reference
Logic Functions
Learn the basic uses of >, <, =, <>, AND and OR
When to use
If you need to test if a cell meets a criterion and only need to TRUE or FALSE result.
Instructions
- There is no “not equal to” button on a keyboard, so “<>” is used to indicate not equal to
- To test if text meets a certain criterion, one will probably use an = or <> in a logic test
- Number tests become more interesting as it can be >, <, <>, =, or even a calculation test
- Use AND() if you need all criteria to be TRUE
- Use OR() if you only need one of a range of criteria to be TRUE
- 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.