Max and Min as an alternative to If

Excel max and min are really simple to use.  You point Excel to a set of numbers.  Max will pick out the largest.  Min will select the smallest.

Often you’ll see people using Max and Min as an alternative to Excel’s If function.  If you haven’t seen that done before it may be slightly surprising.  It’s not better or worse, it’s just an alternative. 

Those ‘in the know’ will sometimes use Max or Min simply because they end up being shorter and neater (and, in our Excel work, we’re always trying find a solution that gets us to an answer more directly – which has to be better that a longer more convoluted alternative).

Max and Min: an example

Here’s an example showing how Max or Min could be used to create a formula that’s shorter than If.

Imagine your model is generating cash flow with that cash accumulating on the balance sheet.  In periods of negative cash flow the balance could turn negative and become an overdraft liability on the balance sheet.  ‘If’ the cash balance becomes negative it should appear as a liability.  ‘If’ the cash balance is positive it should present as a cash asset on the balance sheet.  By default you might think of using Excel’s If function to rise to that challenge, and there’s nothing wrong with that.

Max or Min can shorten If

Here’s an example that shortens the If function by using the alternatives of Max and Min.  When the cash balance is negative, we want the lesser (the Min) of zero and the overdraft appearing on the balance sheet.  We could use the Min function to allocate a negative cash balance into liabilities.

Similarly, we could use Max to make sure all positive cash balances appear under assets.

Shorter and neater is better

Excel’s If function is absolutely splendid.  You want it sitting near-to-hand towards the top of your day-to-day toolkit of essential Excel functions. 

But, when you see someone solving the same kind of problem using Excel Max or Min you can be slightly impressed.  Simply because that (we can assume, advanced) Excel user is taking the trouble to keep thinking really hard, always taking the opportunity to displace the slightly longer alternative with something that’s slightly shorter and neater!