A to Z of Excel Functions: The IF Function

Welcome back to our regular A to Z of Excel Functions blog. Today we look at the IF function.

The IF function

So what’s the most Important Function in Excel? Did you realise that’s what IF is an abbreviation for? Not surprising as I just made it up. However, there is some truth in the jest.  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”, that is, 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, this argument 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, this argument will have a default value of FALSE.

This function is actually 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.

Care should be taken with logical tests as this is the source of many, many errors in spreadsheets. Logical tests assess the criterion/criteria stipulated, no more no less. It assumes a binary universe: X and NOT(X). This isn’t always how our minds think, as I will explain with an exaggerated example.

Intrepid explorer Ivor Challenge is lost in the jungle and needs to find shelter for the night as a rainstorm beckons. Immediately ahead is a clearing with two caves. He writes a formula to determine which cave to sleep in:

The logical_test is to check whether Cave 1 contains a bear. As it turns out, it doesn’t so he sleeps in there and is mauled to death by the lioness who was in there.

Next day, his wife, Cher Challenge, goes searching for him, gets tired and comes across the same caves and uses the same formula to determine which cave to sleep in:

The logical_test is to check whether Cave 1 contains a bear. As it turns out, this time there is (together with some human bones) and so she sleeps in Cave 2 and is eaten by the other bear.

When using IF formulas, you need to train yourself to think logically like a computer. Common sense does not apply. Consider the logic function NOT(expression), which is everything that is not equivalent to the expression. The opposite of a boy is “not a boy”: “girl” is incorrect.

Take care with inequalities in particular. The opposite of x is greater than y is either x is less than or equal to y, or NOT(x is greater than y). This is a common error and it has caused embarrassing mistakes time and time again in business.

Returning to the IF function, let’s consider an 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 an #DIV/0! error.  Excel has several errors that it cannot evaluate, such as #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.

This is where IF comes in.  In my example above,

tests whether the Denominator is zero. This is the conditional formula. 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 type of conditional formula 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: personally, 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:

This automates a model and aids management in decision making and what-if analysis. IF is clearly a very powerful tool when used correctly.

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every business day.

A full page of the function articles can be found here.