Locked lesson.

Upgrade

About this lesson

Learn to build standalone logical IF functions and make them more complex by nesting AND and OR within them.

Exercise files

Download this lesson’s related exercise files.

Logic Functions - Begin.xlsx
27.8 KB Logic Functions - Complete.xlsx.xlsx
25.3 KB

Quick reference

Logic Functions

Building standalone logical IF functions and making them more complex by nesting AND and OR within them.

When to use

Constructing models that allow you to dynamically react to changes in inputs, returning different results based on the inputs.

Instructions

The IF Function

  • Syntax: =IF([Scenario to test],[What to do if TRUE],[What to do if FALSE])
  • =IF(A1>10000,”We’re rich!”,”We need money”) returns “We’re rich” if A1 is greater than 10000, and “We need money” if A1 is less than or equal to 10000.

Key points to remember: The “Scenario to test” must always be something that evaluates to TRUE or FALSE.

AND vs OR

  • =AND(A5=5, B5=6) will return TRUE if A5=5 AND B5=6.  (Both conditions must be met.)
  • =OR(A5=5, B5=6) will return TRUE if either A5=5 OR B5=6.  (Either condition can be met.)

Key points to remember: An AND or an OR function can be nested inside an IF function’s “Scenario to test”, allowing it to dynamically react to changes in our data setup.

NESTING

  • =IF(OR(A1>10000,B1<100000),”We’re rich!”,”We need money”)
  • Will return “We’re rich” if A1 is greater than 10,000, OR if B1 is less than 100000
  • If neither of the above are true, it will return “We need money”

Hints & tips

In addition to the ones covered here, there are many other logic functions available in Excel.

Login to download
  • 00:04 For this video, what I want to do is I want to take a look at this list of
  • 00:08 parts over here and I want to build an audit list.
  • 00:12 And my first criteria that I'm looking at to decide whether I'm going to go and
  • 00:15 check the stock and things like that is whether or not the list price equals $50,
  • 00:20 because I don't really want to go and do an inventory on everything.
  • 00:23 Now in order to do this,
  • 00:24 I'm going to use arguably the most important lookup function in Excel.
  • 00:29 It is the IF function and this this is just magic and
  • 00:32 so important to be able to master.
  • 00:34 So, we're going to start with, =I and
  • 00:37 that's pretty much about as much as you have to type,
  • 00:40 you can now just hit the tab key, because IF is the first function under I.
  • 00:44 Now It then asks you for a logical test or hypothesis to check.
  • 00:49 So what we're going to do is we're going to go and check if B9,
  • 00:53 our list price is greater than 50.
  • 00:55 Whatever you put in here, this logical test must equate to either true or
  • 01:00 false as a result, okay, that's the big secret here.
  • 01:03 I'm then going to move into the optional parameters.
  • 01:06 If I leave these off, this will just return true or false.
  • 01:09 But I'm not going to do that because I want to return a custom message.
  • 01:13 If the list price is greater than 50, I'm going to return the text audit.
  • 01:18 And in order to return that as a textual result, I need to wrap it in quotes.
  • 01:22 So I'm going to go in and say, Part of this, close the quotes.
  • 01:26 If it's not true, if it's less than 50, we're just going to go and ignore it.
  • 01:32 Again to return text, it's gotta be returned between quotes, okay?
  • 01:36 And once I hit edit, you can see that we get in this case here that our brake
  • 01:41 rotors front should be ignored.
  • 01:43 But if I go and copy this down we've got a few that needs to be audited looks like
  • 01:48 caliber front we got 58 that's more than 50 definitely needs an audit.
  • 01:52 These two here we've got brake pads and we've got brake drums,
  • 01:56 58 and 200, they definitely need to be audited, everything else can be ignored.
  • 02:01 Okay, so that's good.
  • 02:02 But what if we want to fine tune our list a little bit more?
  • 02:07 And we want to actually say, okay, what if we want to audit things where the list
  • 02:12 price is greater than $50 and the gross profit is greater than 25?
  • 02:16 We now have two criteria that we're going to look at.
  • 02:20 So what I'm going to start with here is I'm going to write a specific function for
  • 02:24 this called, AND, this is another logic function inside Excel.
  • 02:27 And it's going to look like this, it's just A-N-D.
  • 02:30 And what it does, it asks for one or more logical arguments.
  • 02:35 So the first thing we're going to look at is, is list price greater than 50.
  • 02:41 And now you'll notice that logical two is optional,
  • 02:44 but we're going to hit the comma and we're going to actually take this one here.
  • 02:47 We're going to check if the gross profit is greater than 25.
  • 02:52 And hit close the parenthesis on this and hit enter and it comes back and
  • 02:57 says false.
  • 02:58 Now why is that?
  • 02:59 Well because the list price is not greater than 50 and
  • 03:02 therefore this can't possibly be true.
  • 03:04 Let's roll this one down and see what we get.
  • 03:07 So in the case of our item here with list price $50 that we wanted to audit,
  • 03:11 notice that it comes back and says this one's false, doesn't need an audit, why?
  • 03:16 Well, because even though the list price is greater than 50,
  • 03:20 the gross profit is not greater than 25.
  • 03:22 In order to return a true result, as you see here,
  • 03:26 both the list price has to be greater than 50 and the gross profit must
  • 03:31 be greater than 25, and this is the only place where it happens.
  • 03:36 Now, this is a big thing, every single argument that you put into or
  • 03:40 every parameter you put into that AND must be true in order to return a true result.
  • 03:44 Now, where would we use this though?
  • 03:46 We're certainly not going to use it like this, nobody wants to see a list or
  • 03:49 trues or falses.
  • 03:50 But, remember what I said about the IF function,
  • 03:54 that logical test must return a true or false result, so check this out.
  • 03:59 Let's come back to the beginning here and say equals IF open parenthesis,
  • 04:04 we're now taking the end function that we have and using that as our logical test.
  • 04:11 If it's true, we will audit it, otherwise, we will ignore it.
  • 04:17 The same as we did before.
  • 04:18 Now I'm actually going to do something a little bit different here with the ignore.
  • 04:22 What I'm going to do is I'm actually going to take all the text out, so
  • 04:26 it's just quote, quote.
  • 04:27 Reason being everything between the quotes will be returned to the cell,
  • 04:32 which is nothing.
  • 04:33 And the reason I'm doing this is because this now makes it very clear what needs
  • 04:38 to be audited and doesn't make noise with the stuff that doesn't.
  • 04:42 Now that's great, that gives us a really short list of things to audit.
  • 04:45 But you know what, maybe the criteria is not right here.
  • 04:50 Maybe we should be doing something different.
  • 04:52 We're going to use instead of an AND, we'll use an OR, so we're going to check
  • 04:58 if the list price is greater than 50 or if the gross profit is greater than 25.
  • 05:04 So we'll start again with an OR statement here.
  • 05:06 Notice this gives us false when we roll these down, over here list price is
  • 05:11 greater than 50, even though the gross profit is not greater than 25.
  • 05:16 The OR command says,
  • 05:17 you're going to return true because at least one of my conditions was met.
  • 05:22 That's what's different between AND and OR.
  • 05:24 In AND everything has to be met, in OR
  • 05:26 any of the conditions you provide need to be met in order to return a true results.
  • 05:31 Once again, now that we know that the OR function returns true or false,
  • 05:36 we can use that for the first parameter of our IF statement.
  • 05:40 So once again, I'm going to go audit, otherwise, quote,
  • 05:44 quote to return an empty cell.
  • 05:46 And now, if I hit down Ctrl, enter, because I'd selected all these cells
  • 05:50 beforehand, you can see exactly what items should be audited and
  • 05:54 which ones we can just leave alone.
  • 05:56 So there you go.
  • 05:57 There's some cool use of IF, AND, and OR,
  • 06:00 together in order to build some complex conditional logic.

Lesson notes are only available for subscribers.

Gift this course