Locked lesson.

Upgrade

About this lesson

The key to adding dynamics to your dashboard starts with Excel's Logic Functions.

Exercise files

Download this lesson’s related exercise files.

Logic Function Review.xlsx
29.9 KB Logic Function Review - Completed.xlsx
30.6 KB

Quick reference

Logic Function Review

A review of logic functions in Excel.

When to use

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

Instructions

Equality Testing

  • Testing a cell versus another cell or text via the following format =A1=B1 or =A1=”Product”
  • Results will be TRUE if the data matches, or FALSE if it does not

The IF Function

  • Allows you to return different results in the case of a TRUE or FALSE result from a test
  • Basic Syntax: =IF([Scenario to test],[What to do if TRUE],[What to do if FALSE])
  • Example: =IF(A1>0,”Bank is positive”,”Funds required!”)

Working with AND vs OR

  • Allows combining multiple tests
  • =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.)

Hints & tips

  • The “Scenario to test” must always be something that evaluates to TRUE or FALSE
  • IF statements can be nested to make more complex tests
Login to download
  • 00:05 Before we can move on to formula based formatting rules, we need to to a quick
  • 00:09 logic function review to really crystallize what returns true or
  • 00:13 false values in Excel.
  • 00:15 So we're going to start by selecting our cells here from F4 to F10.
  • 00:20 And what I'd like to do is I'd like to compare to see if the value in
  • 00:25 column B equals tent.
  • 00:27 If it does, I'd like it to say true, and if it doesn't, I'd like it to say false.
  • 00:31 The easiest way to do that is to say equals, or click on cell B4 to start with.
  • 00:36 And then we'll type in, again, equals, quote, tent, quote.
  • 00:42 What this is doing is it's saying is the cell in B4 equal to tent.
  • 00:45 If it is, return true, otherwise, return false.
  • 00:50 If you don't know this trick, if we have a whole bunch of cells selected,
  • 00:53 which we do, right now, I'm still in edit mode for my formula.
  • 00:57 I can hold down my Ctrl key and press Enter, and
  • 00:59 it will commit the exact same formula into all cells.
  • 01:02 I'll be doing that a lot because that's the fastest way to commit cell formulas.
  • 01:07 You'll notice that the first four values here are indeed returning true,
  • 01:11 because in column B, we have tents.
  • 01:14 When we look at row 8, ground sheet is not ten and therefore it's returning false.
  • 01:20 So that's useful.
  • 01:22 But it's more useful when we can actually work with it in an IF statement to say
  • 01:27 if the product equals 10, I will I would like to return the price.
  • 01:31 So to do this we use the IF statement.
  • 01:34 Equals IF, open parenthesis, and it says what's the logical test.
  • 01:39 What I'd like to do is I'd like to test IF B4 equals,
  • 01:43 between quotes, tent, and the comma.
  • 01:47 What would I'd like to do if this is actually true?
  • 01:50 Well, if it's true, I'd like to return the price from D4,
  • 01:55 comma, if it's not true, I'd like to return zero.
  • 01:59 I'm going to close my parenthesis, and again, at this point, I'm going to hold
  • 02:03 down control and press Enter and it will write it into all of the selected cells.
  • 02:07 And you can see that everywhere that we have tent,
  • 02:10 it is nicely returning the value.
  • 02:12 Where we don't have tent, it's returning 0.
  • 02:15 So that works out quite nicely.
  • 02:19 Now what I'd like to do is to play around and find out, can I test whether or
  • 02:23 not two things are true?
  • 02:25 So I'm gonna select again cells H4 down to H10.
  • 02:29 And in this case, I'm going to look to see whether or not the product equals ten, and
  • 02:34 the size in persons equals 3.
  • 02:37 So to do this, we're going to type in equals, and we're going to go with AND.
  • 02:42 AND, when I open it up, allows me to make two tests, so
  • 02:46 I can see does tent equal tent.
  • 02:50 And does size and persons equal, this is a value, so we'll say 3.
  • 02:58 No quotes needed for values.
  • 03:00 Close our parenthesis, hit Ctrl+Enter.
  • 03:04 And you'll notice that because the row four has a four person tent,
  • 03:09 even though that it's a tent, it's not three persons, therefore it's false.
  • 03:14 Row five has a tent which is three persons, and therefore returns true.
  • 03:19 Row six and seven both have tents, but again they're not three persons,
  • 03:23 therefore they return false.
  • 03:25 And even though the fly sheet is three persons, because it's not a tent,
  • 03:30 also returns false.
  • 03:32 So, every condition must be met in order for an and statement to return true.
  • 03:37 So how can we make use of that?
  • 03:39 Well, again, we can wrap that in an IF statement.
  • 03:43 We can say equals IF and then we can check our AND
  • 03:49 condition to say if the tent equals a tent,
  • 03:54 then if the size and persons equals three,
  • 04:00 close our parenthesis.
  • 04:03 So we'll test, are both of those true?
  • 04:05 If so, return the product name.
  • 04:09 Otherwise, I'd like a blank.
  • 04:12 To get a blank,
  • 04:14 two quotes because everything between the quotes is returned as text.
  • 04:18 Close that parenthesis, hold that Ctrl+Enter and
  • 04:22 I now see that we have tent.
  • 04:26 I'm good to go.
  • 04:28 What about if I want the product name if the product equals tent, or the size and
  • 04:33 person that equals 3?
  • 04:35 Well here's what I'm gonna do.
  • 04:36 I'm gonna write basically the same thing.
  • 04:38 As a matter of fact,
  • 04:39 I'm gonna copy this entire formula from the formula bar in the.
  • 04:42 And this is a key piece here.
  • 04:43 I'm not copying the cell, I'm copying from the formula bar,
  • 04:46 because I haven't set up any absolute or relative references here.
  • 04:50 I'm now gonna come over to the cell, go into edit mode, and past it.
  • 04:54 So now what I should see is when I've pasted this before so
  • 04:57 it should still be pointed to the cell that says tent.
  • 05:00 It should not have changed up to C4.
  • 05:01 If you'd copied the formula and pasted in the cell, it would now be equal C4 and D4,
  • 05:06 and that's not what we want.
  • 05:08 So this is all fine.
  • 05:10 The only thing I'm changing, AND now becomes OR.
  • 05:15 Going to hit Enter, I'm gonna go and fill this down my table.
  • 05:19 I will just double-click on the fill handle and it'll fill it all the way down.
  • 05:23 And now what you can see is that if the product name equals tent or if
  • 05:28 the persons equals 3, like the case of Fly Sheet, it will return the product name.
  • 05:32 So the only difference here is that we used an OR instead of an AND.
  • 05:36 And an OR will trigger a true statement if any of the conditions are met,
  • 05:41 where an AND requires all of the conditions to be met.
  • 05:44 This is a key thing working with this true/false testing to make use of
  • 05:48 conditional formatting rules that rely on formulas, which we'll look at next.

Lesson notes are only available for subscribers.

Gift this course