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
27 KB Logic Functions - Completed.xlsx
29.8 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 Logic functions are what truly make Excel sing.
  • 00:07 And the reason for this is because we can use them to look at specific cells and
  • 00:11 react differently depending on what's in them.
  • 00:14 This allows us to start building really complex business solutions out of what
  • 00:17 might appear to be static cells, but cells that can change in future.
  • 00:21 Which is really really cool and really really powerful.
  • 00:24 Now the way that we do this is we start by using an if function, and
  • 00:28 we'll feed it a logical task to a value if it's true or false, and
  • 00:32 then we can also feed the function the ability to react differently depending on
  • 00:36 whether true or false values return by the logical task for the hypothesis.
  • 00:41 So let's try this.
  • 00:42 We're going to try working here to see if the list price in column d is greater
  • 00:47 than 50.
  • 00:47 If it is, we'll returned the message, meets criteria and if it's not
  • 00:52 will return the message, ignore so to start with will type in equals If.
  • 00:58 I'll just hit tab and
  • 00:59 now I'm going to check if the list price in D7 is greater than 50.
  • 01:05 If it is, I want to return a text message, which I need to wrap it in quotes.
  • 01:10 So we'll go quote, meets criteria,
  • 01:17 then we'll close the quotes and then say.
  • 01:20 What we would like to do if the tests fails.
  • 01:23 So the value of false is gonna be ignored.
  • 01:26 I'll close the quotes and close the parenthesis.
  • 01:29 And at this point the formula is complete.
  • 01:31 And when I hit enter, you'll notice that it tells me to ignore.
  • 01:34 And the reason being is that the list prices is not greater than 50.
  • 01:38 If I now go and extend this formula all the way down this range,
  • 01:42 you'll notice that in row 8 it meets criteria, and that's because D8 is 58.
  • 01:48 Also, D10 and when we look at D11 it's 200,
  • 01:52 which also meets the criteria, everything else is marked as ignore.
  • 01:57 So this is pretty powerful.
  • 01:58 Well, let's see how we can actually extend this a little bit further.
  • 02:01 I would like to write a test to see if the list price is greater than 50 and
  • 02:06 the gross profit is greater than 25.
  • 02:08 So to use this, I'm gonna work with the and function.
  • 02:13 The first thing I'm gonna check is, is the list price greater than 50?
  • 02:18 And then you'll notice that it gives me the option, if I press the comma,
  • 02:21 to go to a logical two.
  • 02:23 And it even gives me the option of more.
  • 02:24 So I can put as many of these tests as I want in here.
  • 02:27 I'm now gonna check if the gross profit Is greater than 25, which obviously it isn't.
  • 02:33 Now I can continue writing more tests.
  • 02:35 The key here is that every one of these must return true
  • 02:38 in order to return a true result.
  • 02:40 And therefore, we can see that it comes back with false because
  • 02:43 neither of these two tests is actually passing.
  • 02:46 And if I roll this down you'll notice that there's actually only one record
  • 02:50 that returns true as the output.
  • 02:52 And the reason being?
  • 02:53 The list price here, is 200 which is greater than 50 and
  • 02:57 the gross profit is greater than 25.
  • 03:00 Notice that on row 9, even though the gross profit is greater than 25.
  • 03:03 The list price is still under 50 and therefore it returns false.
  • 03:07 But how can we use this?
  • 03:09 Well, the and function returns a true or false result and that's pretty neat
  • 03:12 because it means that we could go here and say right after equals.
  • 03:16 Let's put in If, open parenthesis,
  • 03:19 this returns the logical test results that we need, we can now say,
  • 03:24 if the value was true the it meets criteria.
  • 03:29 Otherwise ignore it.
  • 03:33 And now in close the parenthesis and roll this down.
  • 03:38 And you can see that indeed the only line that fits the criteria
  • 03:42 is row 11 that had those two pieces that returned a true result.
  • 03:45 So the and function can be used to check multiple things and if everything is met,
  • 03:50 it provides a true result which can be fed into an if statement.
  • 03:53 The or function works slightly differently.
  • 03:56 I'm gonna actually put this directly into the if statement right off the bat.
  • 03:59 So we'll say if or and we'll check if the list price is greater than 50 or
  • 04:06 if the gross profit is greater than 25.
  • 04:10 If that's true, we'll put in meets criteria.
  • 04:16 Otherwise, Ignore.
  • 04:21 We'll hit Enter, we get a result of Ignore on row 7.
  • 04:25 We'll roll this down, and
  • 04:27 you can see that we have a few that meet Criteria where they didn't before.
  • 04:32 So why?
  • 04:32 Well, the list price is greater than 58.
  • 04:34 Even though the gross profit is less than 25, it doesn't matter because we set
  • 04:39 either of these two things were triggered through result.
  • 04:42 So in this case, the OR returns true and
  • 04:44 therefore, we go to the first part of our statement to return value of true.
  • 04:49 So this is how we can use AND
  • 04:51 and OR together with statement to dynamically react to the values.
  • 04:55 And the cool thing here, if I were to go and say,
  • 04:57 you know what, let's take this guy here, and let's go and jump this up to 56.
  • 05:01 And just like that, the results in I8 have changed to reflect the new value.
  • 05:08 And this starts to show the serious power of an if statement.
  • 05:11 It dynamically reacts to the values we have and
  • 05:14 can feed out different messages based on what's happening.
  • 05:16 So if I were to press control said and set this back,
  • 05:20 boom it's back to ignore just as you would expect is going to happen.

Lesson notes are only available for subscribers.

Gift this course