# A to Z of Excel Functions: the IF Function

11 May 2020

*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 **I**mportant **F**unction 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:

**=IF(logical_test, [value_if_TRUE], [value_if_FALSE])**

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:

=IF(Cave 1 has a bear, sleep in Cave 2, sleep in Cave 1).

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:

=IF(Cave 1 has a bear, sleep in Cave 2, sleep in Cave 1).

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,

**=IF(Denominator=0,,Numerator/Denominator)**

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:

**=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 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. *