IF. Perhaps the most commonly used Excel function in financial modelling. My early models were almost entirely created around this function. There is nothing intrinsically wrong with an IF function. However, nowadays I hardly ever use it. Why?
- There are usually more efficient functions for the task in hand, resulting in shorter formulae.
- Logic can easily become confused with an IF function.
- IF functions are heavy on processing power, making models larger.
One of the challenges of modelling is learning to pick which functions to use in different situations. The IF function is usually one of the first functions we learn and habits are always hard to change. However, IF is rarely the best choice of function.
IF functions can generally be replaced with logical statements, MIN, MAX, CHOOSE or INDEX with MATCH. In this blog I’ll discuss logical statements and will return another time to the other alternatives to IF.
What is a Logical Statement?
A logical statement is simply a formula which is either true or false. If it is true, it has a value of 1. If it is false the value is 0.
Here are some of the situations where I would use logical statements instead of IF functions:
EXAMPLE 1:
IF Function:
= IF ( x = y , 1 , 0 )
If x = y, then 1, otherwise 0.
Logical Statement Alternative:
Consider:
= x = y
when x = y, the result = TRUE
when x ≠ y, the result = FALSE
TRUE has the numerical value of 1,
FALSE has the numerical value of 0.
Therefore for:
= (x = y) * 1
when x = y, the result = 1
when x ≠ y, the result = 0
EXAMPLE 2:
IF Function:
= IF ( x = y , a , 0 )
If x = y, then a, otherwise 0.
Logical Statement Alternative:
= (x = y) * a
when x = y, the result = a
when x ≠ y, the result = 0
EXAMPLE 3:
IF Function:
= IF (AND ( x = y , m = n ) , 1 , 0 )
When x = y and m = n, then 1, otherwise 0.
Logical Statement Alternative:
( x = y ) x ( m = n )
when both x = y and m = n, the result = 1
Otherwise the result = 0
This is because TRUE x TRUE = 1
Anything x FALSE = 0.
EXAMPLE 4:
IF Function:
=IF ( x = y , “OK” , “ERROR” )
Logical Statement Alternative:
= x = y
when x = y, the result = TRUE
when x ≠ y, the result = FALSE
This is particularly useful for checks
In summary, try and use a logical function, not an IF function, when one answer is potentially equal to false or zero.
Avoiding IF functions in financial models when possible makes models faster and easier to follow. This should also reduce errors. I have certainly been converted over the years and use IF functions only very occasionally.