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
9.5 KB Logic Functions - Completed.xlsx
10.3 KB Logic Functions - Extra Practice.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:03 So in this example what we're going to do is we're going to work with logic functions and we're going to
  • 00:07 work with a specific pile of data that was pulled off the Internet from a hospital site in the USA
  • 00:13 that list treatment costs for different medical conditions.
  • 00:17 For the case of this example we are going to assume that the patient is put on the wait list
  • 00:22 or put into immediate surgery strictly based on the financials, which we hope is not the case.
  • 00:25 But that's what we are going to use for our scenario right now.
  • 00:29 So the first thing that we are going check is if the treatment cost to the hospital is greater
  • 00:33 than $200,000 then we'll act on them immediately because there is lots of profit there. If it's not then we'll put them on the waitlist.
  • 00:38 The way we test that is by using the IF function. So say =IF
  • 00:43 we'll look at the the treatment costs and ask if it's greater than $200,000.
  • 00:50 If it is we will feedback the name, this is the value of true, we will feedback the name of the actual operation.
  • 00:56 Otherwise we will feedback some text information and it's going to say wait list
  • 01:02 and you'll notice that the text that we're going to feedback is between 2 quotes. Everything between 2 quotes is treated as text. Close that
  • 01:09 bracket hit Enter and you'll see that because this is greater than $200,000 it returns the name of the operation. And if I roll this down, there we
  • 01:17 go, we can see that everything that's under $200,000 is going to the waitlist right away.
  • 01:22 Now what if we wanted to say well we'll actually put these guys forward onto this thing if it's greater than $200,000 or if it's less than 50,000.
  • 01:32 How would we do that? We have a little function that we can use that we'll use inside an IF function later
  • 01:36 but we can test if one of two conditions are met.
  • 01:40 The function for that is called OR
  • 01:43 and what this does is we can actually look at the first cell and say is this less than 50,000?
  • 01:51 And then we put in a comma and this is another test that it's going to perform for us.
  • 01:55 We can now feed it back and say is C4 greater than $200,000?
  • 02:00 And what will happen in this function is it will check and say is this true or is this true
  • 02:05 and if any of the conditions that are in here are true it will return true.
  • 02:10 And if they're not,
  • 02:12 let's see we'll scroll this down here we've got a couple here that are false so $76,000 is not less than $50,000 it's not greater than $20,000, sorry
  • 02:20 $200,000. So it's returning false where
  • 02:24 all these guys here that are less than 50,000, it's less than 50,000 may not
  • 02:29 be greater than 200,000. So it's actually returning true so this is an alternate test that we can do to check if one of two conditions are met
  • 02:37 Likewise we can also check if both conditions are true.
  • 02:40 Let's see if, lets be hypothetical and say this is truly profit driven we're only going to put somebody in for immediate surgery
  • 02:49 if the treatment cost is greater than $200,000 and the profit, the gain on this treatment
  • 02:54 is greater than $50,000. Horrendously profit motivated.
  • 02:58 Well for that we can use =AND this will test to see
  • 03:02 if this number is greater than $200,000
  • 03:06 or
  • 03:09 well sorry, and, we'll check the gain here to see if that's greater than $50,000.
  • 03:16 When we run that in this one comes back and says false because this is a negative number.
  • 03:20 When we roll this down our column here we can see that these two guys
  • 03:25 both have revenues greater than $200,000
  • 03:29 and their profits greater than 50,000 so they give different answers than the OR function as you can see.
  • 03:35 How do we use these though? Well what we can do is we can actually take this formula that we've written and
  • 03:40 we know this is working all the way down the column and I'm going to copy this with CTRL +C
  • 03:45 and now the way that we use the OR and the AND functions we actually nest it right inside an IF function. So we can say IF,
  • 03:53 I'm going to paste that logical test right in there,
  • 03:56 and then it says great what do you want to do if this is true?
  • 03:59 Well now we go back and we say we want the name of the operation
  • 04:02 and if it's not then I'm going to go in and type in between those quotes: wait list.
  • 04:08 And when I hit Enter on this now it takes this test and it says this was false so I'm going to put it on the wait list
  • 04:15 and when I run this down you can see that the heart transplant and liver transplant, purely on a
  • 04:20 profit basis, we are going to go and do right away. Everything else is going to go onto the waitlist until
  • 04:24 we have a little bit more time. So that's the basics of how we use IF, AND, and OR together you can just switch an OR statement
  • 04:33 in place of the AND statement that we did in that last test.

Lesson notes are only available for subscribers.

Gift this course