Another alternative to shortening Excel’s If function
Recently we were looking at how to use Excel’s Max or Min as a more compact alternative to the regular If function.
Excel gives us so many choices! We only need one that works but, if we can find a shorter solution – that’s generally something to embrace and celebrate. That’s why sometimes you see people doing something different than using the day-to-day If function – you’re seeing the result of their successful hunt for something that becomes a fraction neater.
Before we were using Max and Min to allocate our cash balances to one side of the balance sheet or the other. If the cash balance is negative it ends up on the balance sheet under liabilities. When it’s positive it shows up on the assets side of the balance sheet.
The If function's logic test
Do you ever use Excel’s function dialog box? It’s a handy way of unpacking a function. You get into the cell that’s got the function in, then “shift F3” (at the same time) to throw up the dialog box.
Notice the example above, and notice – in the top of the dialogue box – the logic test that’s part of the If function. Advanced modellers know you can multiply a number through by that logic test and it will work just fine. And that gives us another shortened form of the regular If function.
Now here’s the same example as previous, where we’re allocating cash to one side of the balance sheet or the other. But this time we’re using the logic test on its own and multiplying through. When the logic test fails, the test result becomes zero and nothing appears. When the logic test is true the test result becomes 1 and multiplying the cash balance by 1 means the cash balance appears where we want it to appear.
A more complex example
On the day when you’re tempted to put an If function inside an If function (and being an advanced modeller on the hunt for short solutions that are easy for other mere mortals to understand) you’ll be looking for something that’s shorter neater better and more readily inspected. You’ll have lots of alternatives available to you. I’d start by breaking something that’s starting to get complex into more rows. That’s much better than the alternative of putting functions inside of functions inside of functions.
I might also be thinking that Index and Match will have a role (because, after all, that’s just like the always-safe Toyota Landcruiser that is likely to keep you out of trouble when you’re stuck out in the desert).
But I might also be thinking ‘Choose’ because, with a small data set that’s dotted throughout the model (rather than in one continuous list) that might end up slightly simpler than even the mighty Index.
What I wouldn’t be doing is thinking of putting an If function inside an If function – just because that one gets long quickly. There are so many more compact options should I be even vaguely tempted to do that. For a start, you’ve got IFS. And then you’ve got the newer “Switch”.
But let’s imagine, for some reason, the logic you’re wrestling with is particularly convoluted. After thinking through all your options (break it into more steps, Index, Choose, IFS, Switch) you remember the thing about Excel being able to process a logic test just fine.
Let’s look at what that alternative has to offer on the day when it seems the model logic is getting almost-unavoidably complex.
Some days it helps to know you can multiply by the logic test
Some days (in fact, pretty-much every day) when you’re working with Excel you feel lucky. You’re blessed with an embarrassment of riches. There can be so many solutions to the same Excel modelling challenge. Faced with multiple alternatives, perhaps briefly confused and perplexed, you remember you only need one solution. And if it works, you understand it, and it’s not too complex – you pat yourself on the back for creating something that does the job. Then you move on.
But if what you’re working on has become longer and more complex, you might find yourself experimenting with alternatives trying to find a shorter neater alternative to the original attempt. Breaking things apart into more rows or columns might help. Swapping functions might help. Picking out a raw logic test and multiplying through by that might be something that helps too.
