Tips for evaluating conditions in Excel

For many of those who create financial models, the most important function in Excel is the IF function. Financial professionals can use IF in various ways to solve common issues they encounter. This article examines the best ways to employ the IF function — as well as another champion for evaluating conditions, flags — with financial models.

IF at first

The syntax for IF demonstrates just how useful this function is for financial modelling: =IF(logical_test,[value_if_TRUE],[value_if_FALSE]).

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 an example, see the screenshot "Using the logical_test to Avoid Division-by-Zero Errors".

Using the logical_test to avoid division-by-zero errors

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, and #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's crashing and/or corrupting the file.

This is where IF comes in. In the example above, the formula =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, 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, with the formula =IF(Decision_Criterion=TRUE,Do_it,Don't_Do_It). 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 with =IF(IF(IF(IF..., 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, with the formula =IF(condition1=TRUE,IF(condition2=TRUE,IF(condition3=TRUE,1,),),).

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 dependent arguments are TRUE, ie, with the formula =IF(AND(condition1,condition2,condition3),1,).

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, formulas may look complex quite quickly. There is an alternative: flags. In the most common form, flags are evaluated as =(condition=TRUE)*1. Using condition=TRUE will give rise to a value of either TRUE or FALSE; the 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 formulas in the screenshot "Using Multiple Flags for AND".

Using multiple flags for AND

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 through 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 more conditions and rows easily). This produces an AND flag, ie, all tests have to be true. If I wanted the flag to be a 1, as long as one of the above conditions is TRUE (ie, creating an OR condition), that is easy, too, as shown in the screenshot "Using Multiple Flags for OR".

Using multiple flags for OR

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 as shown in the screenshot "Using the Flag Approach to Evaluate the Quotient Numerator/Denominator".

Using the flag approach to evaluate the quotient Numerator/Denominator

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, spreadsheet modelling, and the Power BI suite of tools. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.