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.xlsx
17.2 KB Logic Functions - Completed.xlsx
17.4 KB

Quick reference

Topic

Logic functions.

Description

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

Where/when to use the technique

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

Instructions

The IF Function

  • Basic 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”
Login to download
  • 00:04 >> In this video, we're going to look at basic logic functions in Excel.
  • 00:08 And this is where things start to get kind of exciting,
  • 00:11 because now we can actually use Excel to look at a specific range,
  • 00:16 figure out what the value is and react differently in different situations.
  • 00:20 That's what logic functions are all about.
  • 00:22 A case in point might be here, where you've got a list of products on the left
  • 00:26 hand side, with different prices and costs and gross profits.
  • 00:30 And the sales manager wants to know which items he should set his team on focusing
  • 00:33 on, in order to make the most amount of money.
  • 00:35 So he says, well, the first thing I wanna do is I wanna identify which products have
  • 00:39 a price of greater than $50, cuz obviously those are the ones I wanna sell.
  • 00:42 So we say, oh no problem, let's go and do that for you.
  • 00:46 The way we do it is with an if function, so we'll say equals if.
  • 00:50 And what if does is it allows us to put in a hypothesis to test.
  • 00:55 And then it allows to react differently depending on whether or
  • 00:58 not the hypothesis returns true or false.
  • 01:00 So in this case, our hypothesis is gonna be does D4, is it greater than 50?
  • 01:05 If it's greater than $50, I'd like to return a message that says sell these,
  • 01:11 and we put that in quotes because it's text, we say, sell these.
  • 01:16 If it's not greater than 50, I'm gonna say low priority.
  • 01:21 RT there we go, and we'll put in our quotes and close our parenthesis.
  • 01:27 Remember all text needs to be between quotes.
  • 01:29 So we'll say enter, and it says this is a low priority item, but
  • 01:33 if I drag this down You'll notice that it wants us to sell product
  • 01:38 B because the list price is $58, it's greater than 50.
  • 01:42 These ones are a low priority.
  • 01:45 It looks like products D and E are a high priority, we wanna sell these as well,
  • 01:49 so that's great.
  • 01:50 And then the sales manager comes back and
  • 01:51 says you know I wanna refine this a little bit more.
  • 01:53 What I'd like to do is I'd really like to focus on the products
  • 01:56 where the list price is greater than 50, and the gross profit,
  • 02:00 how much money I make, is greater than $10 on each item.
  • 02:03 So we say all right, well, no problem.
  • 02:05 Now in order to do this, what we need to do is we need to use an and
  • 02:09 function to test whether or not both the conditions he's looking for are true.
  • 02:14 This is another type of logic functions, I'm gonna say equals and.
  • 02:18 And it says give me your logical, this is your hypothesis.
  • 02:20 So we'll say what let's check to see if the list price is
  • 02:26 greater than 50 and if the gross profit is greater than ten.
  • 02:31 Okay and if both of those are true the function will come back and
  • 02:36 return true, if not it returns false.
  • 02:39 So, it says this is greater than 50 here, this is greater than ten,
  • 02:43 it's returned true.
  • 02:45 Same with product E, the list price of 200 is greater than 50,
  • 02:48 the gross profit of 50 is greater than 10 and so it returns true.
  • 02:52 All the other items, even where the list price is 58, greater than 50,
  • 02:56 the profit is less than ten, so it will return false.
  • 03:00 Now, you probably don't want to use this function standing on its own like this.
  • 03:04 But what we do wanna do, is we say, you know what, true or
  • 03:07 false values are what gets fed into our if statements.
  • 03:09 So what I'm gonna do, is I'm gonna take this formula that I already have, and
  • 03:13 I'm gonna go right after the equal, so I'm gonna type in equals if, open brackets.
  • 03:18 My logical test for my if function always needs to return true or false,
  • 03:22 that's the hypothesis test.
  • 03:25 And if it returns true, put in my comma here, what would I like it to return?
  • 03:30 I'd like it to return sell these,
  • 03:33 otherwise I'd like it to return low priority.
  • 03:39 Close the quotes, close the brackets, and I'm gonna press command enter,
  • 03:43 to commit this to all the formulas, or all the cells rather.
  • 03:48 You can see now that it is actually reacted to a cell V's
  • 03:53 giving me the message to focus on these.
  • 03:55 The last thing the sales manager thinks is well wait a minute,
  • 03:58 what if I can actually make my money based on volume.
  • 04:02 What if I could actually say, you know if the least price is greater than $40 or
  • 04:07 my profit percentage is greater than 25,
  • 04:10 maybe I could actually make more money if I do that.
  • 04:13 So I'll say all right, this uses a similar formula to and,
  • 04:18 but it actually uses an or function.
  • 04:21 And what or does is it says again provide me with your hypothesis.
  • 04:25 And I'd like to check if 29 is greater than 40 or if profit,
  • 04:32 now this is a decimal number is greater than 0.25, because that's gonna be a 25%.
  • 04:39 If either of those two conditions is met the or function will return true.
  • 04:43 Remember with the and function both conditions have to be met.
  • 04:47 So in this case if either is met we're gonna say you know what,
  • 04:50 give me the product name, I'm not gonna fill in text.
  • 04:53 Otherwise we could say low priority in text.
  • 04:58 So we can use cell references in place of these as well, we'll hit enter.
  • 05:03 And we'll copy it down, and you'll notice that the results
  • 05:07 are different than what we had from the and side.
  • 05:09 A lot of these ones, this one says hey, the list price is greater than 40, so
  • 05:14 even though the profit percentage is actually lower than 25,
  • 05:17 it doesn't matter we'll return it because one of those two conditions is true.
  • 05:21 Same with this guy here, the price may be low but
  • 05:23 the profit percentage is quite high.
  • 05:25 So we want to fucus on selling those.
  • 05:27 So this is how we use basic logic functions inside Excel.

Lesson notes are only available for subscribers.

Gift this course