How to make better use of Excel’s IF function
For many of those who create financial models, the most important function in Excel is the IF function. This article discusses valuable ways to use IF, providing solutions to common issues encountered by finance professionals.
I have heard that there is an alternative to using the IF function in Excel using “flags”. What are they?
The syntax for IF demonstrates just how useful this function is for financial modelling:
This function has three arguments:
- logical_test: This is the “decider”, ie, a test that results in a value of either TRUE or FALSE. Strictly speaking, the logical_test tests whether something is TRUE; if not, it is FALSE.
- value_if_TRUE: What to do if the logical_test is TRUE. Note that you do not put square brackets around this argument. This is just the Excel syntax for saying sometimes this argument is optional. If this argument is indeed omitted, it will have a default value of TRUE.
- value_if_FALSE: What to do if the logical_test is FALSE (strictly speaking, not TRUE). If this argument is left blank, it will have a default value of FALSE.
This function is more efficient than it may look at first glance. Whilst the logical_test is always evaluated, only one of the remaining two arguments is computed, depending upon whether the logical_test is TRUE or FALSE. For example:
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 a #DIV/0! error. Excel has several errors that it cannot evaluate, eg, #REF!, #NULL, #N/A, #Brown, #Pipe. OK, so one or two of these I may have made up, but prima facie errors should be avoided in Excel as they detract from the key results and cause the user to doubt the overall model integrity. Worse, in some instances these errors may contribute to Excel crashing and/or corrupting the file.
This is where IF comes in. In my example above:
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, but you get the picture.
It is my preference not to put a zero in for the value_if_TRUE. I think a formula looks clearer this way, but inexperienced end users may not understand the formula, and you should consider your audience when deciding to put what may appear to be an unnecessary zero in a formula. The aim is to keep it simple for the end user.
An IF statement is often used to make a decision in the model, ie:
This automates a model and aids management in decision-making and what-if analysis. IF is clearly a powerful tool when used correctly. However, sometimes it is used when another function might be preferable. For example, if you find yourself writing a formula that begins
then I humbly suggest you are using the wrong function. And I don’t mean to start using IFS from Excel 2016. IF should never be used to look up data: There are plenty of functions to help with that problem, but we will come to that in time. However, sometimes your logical_test might consist of multiple criteria, eg:
Here, this formula gives a value of 1 only if all three conditions are true. This nested IF statement may be avoided using the logical function AND(condition1,condition2,…), which is only TRUE if all dependant arguments are TRUE, ie:
This is actually easier to read. Two other useful logic functions are sometimes used with IF:
- OR(condition1,condition2,…) is TRUE when at least one of the arguments is TRUE.
- NOT(condition) gives the opposite logic value, so that if the condition is TRUE, it will be FALSE and vice versa.
Even using these logic functions, formulae may look complex quite quickly. There is an alternative: flags. In the most common form, flags are evaluated as
condition=TRUE will give rise to a value of either TRUE or FALSE; the curved brackets will ensure this is evaluated first; multiplying by 1 will provide an end result of zero (if FALSE, as FALSE*1 = 0) or one (if TRUE, TRUE*1 = 1). I know some modellers prefer TRUEs and FALSEs everywhere, but I think 1’s and 0’s are easier to read (when there are lots of them) and, more importantly, easier to sum when you need to know how many issues there are, etc.
Flags make it easier to follow the tested conditions. Consider the following:
In this illustration, you might not yet understand what the MOD function does (check out A Modicum of MOD for more details). But you can follow each of the flags in rows 4 to 7 without being an Excel guru. Row 9, the product, simply multiplies all of the flags together (using the PRODUCT function allows you to add additional conditions and rows easily). This produces an AND flag. If I wanted the flag to be a 1, as long as one of the above conditions is TRUE, that is easy, too:
Flags frequently make models more transparent, and this example provides a great learning point. Often, we mistakenly believe that condensing a model into fewer cells makes it more efficient and easier to follow. On the contrary, it is usually better to step out a calculation. If it can be followed on a piece of paper (without access to the formula bar), then more people will follow it. 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.
Word to the wise
A word of caution: Sometimes you just can’t use flags. Let me go back to my first example in this section – but this time using the flag approach:
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.
Liam Bastick is director of SumProduct, a global consultancy specialising in Excel training. Send ideas for future Excel-related articles to him at firstname.lastname@example.org.