As part of my mission to encourage modellers to reduce reliance on the IF function, this month’s blog looks at how MAX and MIN can replace IF. Last month I wrote about situations where IF can be replaced by logical statements. Next month I will show how CHOOSE and INDEX with MATCH can be used instead of IF.
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.
Here are some situations where I would recommend using MIN or MAX (or a combination):
Example 1: Selecting either the largest or smallest number
IF Function:
= IF ( x > y , x , y )
If x > y, then x, otherwise y.
MAX or MIN Alternative:
= MAX ( x , y )
This formula simply picks up the largest number and its purpose is immediately clear.
The opposite effect can be achieved by using a MIN function.
EXAMPLE 2: Selecting either only positive or negative numbers
IF Functions:
= IF ( x > 0 , x , 0)
If x > 0, then x, otherwise 0. This will therefore only pick up positive numbers.
= IF ( x < 0 , x , 0)
If x < 0, then x, otherwise 0. This will therefore only pick up negative numbers.
MAX or MIN Alternative:
= MAX ( x , 0) will only return positive numbers.
= MIN ( x , 0) will only return negative numbers
EXAMPLE 3: Cash Distributions
Sometimes IF functions are used to model cash distributions in a cascade (or cash sweep or waterfall). Using MIN and MAX instead is more efficient and transparent.
For example, if x is the cash available and y is the maximum payment allowed or required, then:
IF Function:
=IF ( IF (x > 0 , x , 0) > y , y , IF (x > 0 , x , 0 ))
This formula is hard to read, but it picks up the minimum of y and x (as long as x is positive).
MAX or MIN Alternative:
Far more transparent is the equivalent formula:
= MIN ( MAX ( 0, x ) , y )
Translated as:
The minimum of:
- x (which must never be negative), and
- y
EXAMPLE 4: Debt Sculpting
For a more complex cascade (for example debt sculpting), the following situation could arise:
x is the cash available
y is a minimum payment (for example an interest payment)
z is a maximum payment (for example the debt balance at start plus the interest payable).
IF Function:
I would never tackle a situation like this with an IF function but, for entertainment, here goes:
=IF ( IF (x > y , x , y) > z , z , IF (x > y , x , y ))
I’m not even going to attempt to translate this one. It is far better to use MAX and MIN.
MAX or MIN Alternative:
= MIN ( MAX ( x , y ) , z )
Translated as:
The minimum of:
- The maximum of x and y (this therefore forces a minimum payment), and
- z (this makes sure that the maximum payment is not exceeded).
Need I even write a conclusion here? If I haven’t managed to convince you with that one, then I never will.