Locked lesson.

Upgrade

About this lesson

Illustration and issues with the IF function.

Exercise files

Download this lesson’s related exercise files.

IF.xlsx
15.6 KB IF - Solution.xlsx
16 KB

Quick reference

IF Function

Discover how to use IF function in a formula.

When to use

IF function is one of the most important functions in Excel. The syntax for IF demonstrates just how useful this function is for financial modeling.

Instructions

Overview

  • Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE
  • Syntax: IF(logical_test,value_if_true,value_if_false)
  • logical _test is any value or expression that can be evaluated to TRUE or FALSE
  • value _if_true is the value that is returned if logical_test is TRUE
  • value _if_false is the value that is returned if logical_test is FALSE

Example 1

  • In this example, the intention is to evaluate the quotient Numerator / Denominator. However, if the Denominator is either blank or zero, this will result in an #DIV/0! error. Excel has several errors that it cannot evaluate, e.g. #REF!, #NULL, #N/A.
  • This is where IF comes in. In the example above, =IF(Denominator=0,,Numerator/Denominator) tests whether the Denominator is zero, If so, the value is unspecified (blank) and will consequently return a value of zero in Excel. Otherwise, the quotient is calculated as intended.
  • This is known as creating an error trap. Errors are “trapped” and the ‘harmless’ value of zero is returned instead. You could put “n.a” or “This is an error” as the Value_if_TRUE.

Example 2

  • Flags frequently make models more transparent. It is usually better to step out a calculation than to condense a model into fewer cells. If it can be followed on a piece of paper (without access to the formula bar), then it will be easier to follow. If more can follow the model logic, errors will be more easily spotted. When this occurs, a model becomes trusted and therefore is of more value in decision-making.
  • Sometimes you just can’t use flags. Here, the flag does not trap the division by zero error. This is because this formula evaluates to =#DIV/0! x 0, which equals #DIV/0! If you need to trap an error, you must use an IF function.

Login to download
  • 00:04 Let's keep going on the 12 most common functions using financial modeling,
  • 00:09 albeit a subjective list.
  • 00:11 The first of them is probably one of the most important functions.
  • 00:16 IF, important function, I bet you didn't know what IF stood for before, did you?
  • 00:21 Yeah I'm just making it up on the spot.
  • 00:22 So I've already assumed you may have noticed in some of
  • 00:26 the examples before this, that I've already used the IF function.
  • 00:31 It's something pretty much if you've ever used Excel you've probably used, and
  • 00:35 basically, it's like a fork in the road.
  • 00:38 It's like IF, and you apply some logical test, if it's TRUE,
  • 00:42 you do one thing, and if it's FALSE, you do another.
  • 00:45 And the way Excel works is quite simple, if it's not TRUE,
  • 00:49 then it's FALSE, the end.
  • 00:51 There's no sort of third state, so just be careful.
  • 00:55 Sometimes, we will use what are called flags or boolean logic.
  • 01:00 This is an alternative to using an IF statement.
  • 01:03 And what this does is actually put a condition, check if it exists,
  • 01:07 give it to one or zero, and
  • 01:09 then multiplies that by the formula rather than having some complex IF function.
  • 01:13 Let me explain, in row four of this first example,
  • 01:18 I have a whole lot of variables, which go between 0 and 9.
  • 01:23 In cell C6, I put a value of 5, what I want to know,
  • 01:27 is in my row of numbers here, which ones of these actually exceed the value 5?
  • 01:35 I can do this using an if function, so I can type in =if, and notice I typed in
  • 01:39 lowercase, so that when I press Enter, if Excel recognizes it, it will capitalize.
  • 01:45 This helps me identify typos, if I find errors in my formulas, just as a tip.
  • 01:50 If open brackets, the actual value I'm trying to see if it's greater than 5,
  • 01:56 and I'm gonna press F4 function key twice to make it F$4 to anchor row 4.
  • 02:01 So if I copy this down, it will stick to row 4, and that's got to be greater than,
  • 02:06 well, C6 which has to be kept absolute.
  • 02:09 Then put a 1, otherwise a 0,
  • 02:11 that's what I'm testing for, and I can copy this across.
  • 02:15 This indicates which of these numbers has a value of greater than five.
  • 02:23 As you can see here, wonders of conditional formatting.
  • 02:28 Now, a common mistake that gets made in modelling is the following.
  • 02:31 Let me decide what's less than five.
  • 02:38 I can just steal this, put it down here, and
  • 02:43 all I need to do is try to greater than symbol to less than.
  • 02:47 You see why I put the dollar signs in there?
  • 02:49 I'm completely lazy.
  • 02:53 Nothing wrong with being a lazy modeller,
  • 02:56 there is something wrong with lazy modeling.
  • 03:00 So those in red, highlight those to currently less than five, but
  • 03:05 notice, these two haven't been included.
  • 03:08 A lot of people think the opposite of greater than five is less than five.
  • 03:12 Now we also have to consider equal to, so again, if I copy this,
  • 03:20 put it in here, it gives me the same thing at the moment.
  • 03:25 But I'm going to put instead, is equal to, and
  • 03:30 Ctrl+Shift right arrow, Ctrl+R, there you go.
  • 03:38 You see that the opposite of greater than is less than or equal to, and
  • 03:42 the opposite of less than is greater than or equal to.
  • 03:45 And this is a big mistake that often gets made in modeling.
  • 03:48 Now, the alternative to this is to use a flag.
  • 03:51 Let's look at the next sheet, I've already prepared this one.
  • 03:55 And what I've done is I've actually written it like this,
  • 03:58 so you put the actual condition in, that will evaluate to true or false.
  • 04:03 You put a bracket around it so it evaluates first, and multiply it by 1,
  • 04:07 2 times 1 is 1, false times 1 is 0.
  • 04:12 Same idea, so it's not alternative, and this can step it out, and make it simpler.
  • 04:17 You may recall, when we were talking about the transparency right from
  • 04:22 the beginning of this course, I gave the following example.
  • 04:26 I doubt plans are go through this entire example again.
  • 04:29 Except that, what I was trying to work out when the day is a week day and
  • 04:34 the final day of an even month in the second half of 1999.
  • 04:37 Nice Simple Formula, if you use an IF statement and
  • 04:41 you go to took this way you can end up with a horrible nested if like this.
  • 04:47 Whereas if I do use Flags so
  • 04:50 I evaluate whether it's a week day condition in brackets times one.
  • 04:55 Over the dates in the second half of the year, whether the month is even,
  • 04:59 whether the year is 1999, and the date is the end of the month, again just move to
  • 05:04 apply all those together, and wallah I have a much simpler formula.
  • 05:11 So flags can be very very useful as an alternative to it.
  • 05:15 But be careful,
  • 05:17 let's consider this final example, I'm going to an if statement in here.
  • 05:22 First of all, before I put the if statement,
  • 05:24 let me show you why you need the if statements.
  • 05:26 I want to work out the division, equals that divided by that,
  • 05:32 is 75%, brilliant, what if we make that 0?
  • 05:38 Rats, division by 0, but I can put the following in, I can go, okay, if,
  • 05:44 this Divisor equals 0, then I can just put Nothing.
  • 05:50 Put zero for the time being, or
  • 05:54 maybe NA, you could put either.
  • 05:58 That's what we call an error trap, otherwise, at the end.
  • 06:03 Order of calculations, I'll do 3 over 4, it's all fine.
  • 06:07 Now you might think the alternative for the flag is to go equals in brackets.
  • 06:12 This divided by that, so we evaluate that, multiplied by the condition.
  • 06:18 That this is not equal to zero,
  • 06:23 the triple with this is, it doesn't work.
  • 06:28 Because with an IF statement,
  • 06:30 it evaluated this first of all and then only does one of these two things.
  • 06:35 So the numerator of the divisor isn't actually calculated, whereas in this one,
  • 06:40 the numerator of the divisor is always calculated.
  • 06:44 And then any error multiplied by naught to one is always the error.
  • 06:48 So just be careful.

Lesson notes are only available for subscribers.

Gift this course