Show as: Mobile

Jedi Knight Alternatives to Excel’s IF function

Alternatives to Excel’s IF function

If you’ve used Excel at all before you will have come across the IF function. It has to be part of your Excel survival tool kit and you absolutely need to master it.

The IF function: bestowed on us by saintly computer programmers

Assuming you’re familiar with the If function you may know that it arrives to us from the world of computer programming. In computing it would allow us to send our program in one direction IF a particular condition were met. The program would route down a different branch IF the condition were not met.

In Excel If essentially does the same job. IF a particular condition or logic test is true then we’ll get one result (which we can use further down our Excel spreadsheet). If the logic test is not true, then Excel will give us back a different result, potentially influencing subsequent calculations in the spreadsheet.

IF basics

Here’s an example of IF at work. In this example we’re using an If statement to see whether our financial ratios are in line or not. If the outputs of the model are too high we get back the word "breach". If the outputs of the model are fine (i.e. if they’re not too high) we get back the word "OK".

If statement basics

You can click on the picture to download the example. To get that grey box popping up we’ve clicked on the little "fx" button just left of the formula.

If statements are really powerful in computer programming and that’s why the sage Yoda characters behind Excel were wise enough to endow us with them. IF statements have so many applications ("untold" is not too strong a word) that you really do need to master them to survive your financial modelling career.

Jedi Knights and the IF statement alternatives

Assuming you have been modelling for some time and got to grips with IF statements a while ago, you may be aware that there’s a way to recognise other Jedi Knight modellers like you. Jedi know that IF statements are awesomely powerful. But they’ve done enough modelling to know that often there’s a shorter neater alternative to IF statements (shorter and neater must always be better in financial modelling). The first time you see a Jedi using an IF alternative it may confuse you. But once you start to understand it and use it yourself you’ll start to wonder why other modellers are making life difficult for themselves by going to the trouble of writing out a full IF statement. "They’re obviously not Jedi Knights like me" you’ll say to yourself. "They can’t have been modelling that long". Once you see another modeller using the IF alternatives you’ll begin to smile inside and go "Oh, there’s another Jedi Knight like me". Never mind the IF alternatives themselves, what we’ve got here is a tool for spotting Jedi Knight modellers!

How to recognise a Jedi Knight modeller: IF alternative #1

Did you know that the TRUE/FALSE logic test inside the IF function stands alone? And that you can multiply a number through by the logic test? Once you understand this you’ll wonder why (when manipulating numbers) you ever bothered writing out a full IF function before. You’re at risk of (only to yourself please) pouring scorn on all those other modellers who bother to waste keyboard strokes with the full IF function when really they don’t need to.

Multiplying by the logic test: an example

Perhaps it’s best to work through an example (you can click on the example to download it):

Multiplying through by the logic test from an If function

In the example we’re imagining a model that calculates cash balances. Sometimes cash balances are positive, sometimes they are negative. What we want to do in the model is allocate the cash to one side of the balance sheet or the other. So iF cash balances are positive cash ends up on the assets side of the balance sheet. iF cash is negative we want it appearing under short term (say overdraft) liabilities on the balance sheet. It's just a simple example where we have to send numbers in one direction or another.

This cash allocation challenge is easily solved using the standard IF function in lines 9&10. But have a look at what’s happening in lines 12&13. There all we’ve done is taken IF’s logic test, put brackets around it, and multiplied through by the cash balances.

IF alternative #2: max and min functions

Often on our modelling courses we get questions about Max and Min functions. People have seen them in someone else’s model and want to understand what they’re seeing. It’s very easy to understand what's going on: we're seeing a Jedi Knight modeller at work. What we have is just a shorter neater alternative to the full IF function.

Have a look at line 15 in the example.

Max and Min as an alternative to Excel's If statement

In line 15 we’re asking Excel to deliver back to us the biggest of these two things: cash balances or zero. That means that IF cash balances are bigger than zero then we’ll get those positive numbers appearing under our assets. It’s just an alternative to the full IF statement.

At line 16 we’re asking Excel to deliver to us the smaller of cash balances or zero. If cash balances are negative then it’s those numbers that appear on our balance sheet under liabilities.

Have we changed your life today?

OK we’re not pretending that any of this is life changing. Once you understand these simple tricks, whenever you’re dealing with numbers, you’re very unlikely to want to bother writing out a full IF function again. If you like it’s a way of spotting a competent beginner (If statement all day every day) from a modeller who’s been around the block a lot more times and clearly chants "shorter, neater, better" as a mantra to themselves while they’re modelling, eating, sleeping and out at the pub with their very tiny group of friends. If, reading this, you’re feeling like you’re the sort of person who might quite like the idea of starting to mumble "shorter, neater, better" every once in a while then, while not having changed your modelling life today, we may have at least made sure you know you're on the path to modelling enlightenment!

About the author: Financial Training Associates Ltd

Financial Training Associates Company logo

FTA Ltd is a provider of financial training courses. FTA’s financial modelling course runs regularly and includes coverage of helpful Excel tips and tricks.

Looking for professional training?

Tell us what kind of professional training you're searching for and we'll look through our over 17,000 course listings and personally get in touch with several UK training providers. They'll send you more information about the course including price, upcoming dates, and answers to any questions you might have.

This field is used for controlling automatic form submits. Please leave it blank:
Tell us what you're looking for:

This is a cost-free and obligation-free service.

Last updated: 30 Mar 2016

You might also be interested in:

How to Identify & Overcome Complexity: 6 Solutions That Work

Published: 24/07/2017

Over-complexity is the silent killer for many companies, and it’s a proven fact. The largest 200 companies in the Global Fortune 500 listing lose approximately 10.2% of shareholder value every year due to complexity. Read NSF's top tips for cutting through complexity!

Read more

6 tips to help you save money on training

Published: 03/06/2015

Training is by now accepted as a necessary part of employee development, but can be a difficult commitment to honour when budgets are tight.

The key is learning how to save in the right places. Follow these 6 tips to ensure you develop your employees in the most cost-effective way possible.