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.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2013, 2016, 2019/365.

Exercise files

Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.

Logic Functions.xlsx
16.8 KB Logic Functions - Completed.xlsx
17.1 KB

Quick reference

Topic

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”

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

Login to download
  • 00:04 Logic functions are what truly make Excel sing.
  • 00:08 The reason for that is because we can use them to look at specific cells and
  • 00:12 react differently depending on what's in them.
  • 00:15 This allows us to actually start building really complex business solutions out
  • 00:20 of what might appear to be static cells, but cells that can change in the future,
  • 00:25 which is really, really cool, and really, really powerful.
  • 00:28 The way that we do this is, we actually start by using an if function.
  • 00:32 And the syntax for if is equals if open parenthesis.
  • 00:36 What happens then is you have to provide a logical test, some kind of a hypothesis.
  • 00:41 And whatever you put in here must be able to evaluate to either true or false.
  • 00:46 An example here would be where we can actually look at the list price and say,
  • 00:50 is it greater than 50?
  • 00:53 So that can then be evaluated to say, is that true or false?
  • 00:56 We'll put in a comma and Excel asks, what would you like us to do if this is true?
  • 01:01 Well I'm gonna put in some text, and to do that I put it between quotes and
  • 01:04 I say, meets criteria.
  • 01:09 Then we can put in a comma and say if it's false.
  • 01:12 What would you like to do?
  • 01:13 I'll put in some quotes in here, again, and say ignore.
  • 01:17 Okay, so we're gonna focus on the things that are greater
  • 01:20 than 50 as far as our list price goes.
  • 01:22 And we'll say Enter, and it says that we should ignore this product because
  • 01:26 the list price is not greater than 50.
  • 01:27 If I drag this down now,
  • 01:31 you'll notice that we do have some that do meet the criteria.
  • 01:33 Here's list prices is 58, these ones are 58 and
  • 01:37 200, so both of those meet the criteria.
  • 01:39 So that's the basics for how a logic function works.
  • 01:43 And the cool thing here is that if this changes to say 15, it now
  • 01:48 becomes an ignore because the formula is actually looking at the value in the cell.
  • 01:51 Let me just undo that, press control Z.
  • 01:54 What I'd like to do now is focus on an end scenario.
  • 01:59 We want to extend this logic to say, only tells us that it meets the criteria if we
  • 02:05 actually have a list price greater than 50 and our gross profit is greater than 25.
  • 02:10 So in this particular case what we're gonna do is we're gonna
  • 02:12 actually start with a different logic function called and.
  • 02:16 What and does is it allows us to provide multiple logical tests.
  • 02:21 So in this case, we can say, and.
  • 02:23 We'll say is list price greater than 50, comma,
  • 02:28 what's our next logical test, is gross profit greater than 25.
  • 02:34 And you'll notice that logical three is here, so
  • 02:37 I could press my comma and keep adding other things.
  • 02:41 When I close my parenthesis, cuz this is enough for me now, and hit enter,
  • 02:45 you'll notice it comes back and tells me that this is false.
  • 02:48 Because this is not met and this is not met.
  • 02:51 And if I drag these down,
  • 02:53 there's only one place where both of these criteria are successfully met.
  • 02:56 And that's here on the brake drum, where the list price is greater than 50 and
  • 03:01 the gross profit is greater than 25.
  • 03:04 It's important to recognize with the and function that all of the criteria that
  • 03:08 you past to it must evaluate to true in order to get a true result.
  • 03:13 Okay, every single one.
  • 03:14 If even one of them does not, it won't return true, it'll return false.
  • 03:19 But now how can we use this?
  • 03:20 Well, remember what I said with if that the logical test has result in true or
  • 03:26 false.
  • 03:27 Well, these guys do.
  • 03:29 So let's use this in an if function.
  • 03:32 Instead of saying equals and, we'll put in right after the equal sign,
  • 03:35 we'll say if and we'll open our parenthesis.
  • 03:37 And it says what's your logical test?
  • 03:39 Well, I all ready know that this returns to true or false.
  • 03:43 So now I can come back and say comma, meets
  • 03:47 criteria or ignore.
  • 03:53 Now hit enter, it tells me to ignore this one.
  • 03:57 We can drag this down the table.
  • 03:59 And we now show that we only have the one that meets criteria, so that's kinda nice.
  • 04:04 What if though, we wanted to look at this and say, what if it meets
  • 04:07 either condition, then let us know that we should actually be looking at it?
  • 04:12 For that, we have a different function, which is called or.
  • 04:16 Or works virtually the same as and,
  • 04:19 except that it will actually return a true result if any of the conditions are met.
  • 04:25 So in this case we'll say, is D4 greater than 50?
  • 04:28 Or is F4 greater than 25.
  • 04:34 We'll close our parentheses, hit Enter,
  • 04:36 and it tells us that that's false, and drag this down.
  • 04:40 You can see that now, we have a list price that's greater than 50 but
  • 04:45 even though the gross profit isn't greater than 25, it still reigns true.
  • 04:48 As does this one, which has a gross profit greater than 25,
  • 04:52 even though the list price is not greater than 50.
  • 04:55 So let's go unwrap this in a function as well.
  • 04:58 We'll say if right after the equals sign,
  • 05:04 comma, meets criteria or ignore.
  • 05:09 Enter and you can see now that we can get some nice English like messages
  • 05:15 out of our conditional logic that we've actually built, which is fantastic.
  • 05:20 This is what makes Excel really become magical when you're building
  • 05:24 big solutions.

Lesson notes are only available for subscribers.

Gift this course