An If function masterclass

If functions make it to the #1 spot on my top 10 list of Excel formulas.

But, if you know how, you can shorten and simplify If functions. Using a shortened If formula will demonstrate (to anyone who is equipped to realise) that you're a pro who has been around the block a few times. The alternative of using the same traditional If formula all the time is pretty-much the equivalent of leaving fingerprints all over your model that shriek "amateur" in a very loud voice.

A shorter If formula

In today's example let's imagine a series of cash balances. Sometimes cash balances are positive and sometimes cash balances are negative. Imagine we want to allocate cash to one side of the balance sheet or the other. So positive cash balances end up showing as a cash asset on the balance sheet. Negative cash balances appear as say overdraft on the other side under liabilities.

We could think of allocating cash using an "old school" If function. The logic would work something like this. If cash balances are greater than zero then we want the balance showing under asssets. If cash balances are less than zero then we want the balance showing under liabilities.

The example isn't important it's just a chance to play with If. See the screenshot below for the "old school" If function.

The logic test of an If formula stands alone

Below, clicking into B6 I've just pressed "Shift F3" - the shortcut for the insert function dialog box.

Notice that logic test in the top box. It's quite capable of standing alone. That's handy because you can use that logic test to construct simple checks within your model, seeing whether one number equals another (e.g. that assets = liabilities) or whether one number is greater than or less than another number.

Logic test x number = a shorter If formula

At the next step we're going to multiply the logic test (Excel will let you do that) by the original numbers in the cash balances and we're back to where we started - but with a shorter formula now.

Looking like a pro with a shorter If formula

Now we have a shorter If formula we're crowing "pro" rather than shrieking "amateur". Knowing that you can take Excel's logic test and multiply by a number you have to ask yourself: "Why would anyone ever write out a full If formula again?" (when processing numbers through If).

Max and Min formulas as another alternative to If

Sometimes I get questions about Excel's Max and Min formulas. You'll see practised modellers using those just as a shorter neater alternative to If. That's all that's about. If you see someone using Max or Min formulas they're just in the (great) habit of working hard to make their formulas as short as possible.

What If your If is getting more complicated?

Martyn came to me the other day with a question along the lines of: "What if I want one number if this value is between 1 and 11 and another number if it's outside of that range?". He needed to know about Excel's "And". And you can be a Jedi with "And" too. See more Jedi if functions.

What if you want to incorporate project delay?

What if you want to build in the possibility of delay e.g. for something like cap-ex spend? If's humble logic test can help you with that too! Here's how: incorporating project delay.

It's more than just looking like a pro

Getting into the habit of shortening and simplifying your If formulas is more than just about looking like a pro. The simplified If formula is a great tool should you find yourself needing to create a horrible embedded If formula. I would strongly recommend you don't embed, and I would always recommend you break long formulas into steps. But let's just imagine for a moment that, through some fluke of the universe (a warp in the space-time continuum perhaps), you are finding it impossible to make a better formula choice or add a few extra lines or columns and are finding you have to create an horribly-embedded If formula.

Perhaps it's that time of the year when reality gets suspended. Perhaps it's time for the office Christmas party. A time of year when people go wild and normal rules around the extent of restaurant menu choice and value for money (and maybe, for our exercise, Excel good practices) go completely out the window. As part of the Christmas madness perhaps a spreadsheet has gone around the office where people, weeks in advance of sitting down to their meal, have to decide what they want to eat on a particular cold December evening (and, sitting down to eat that evening under the influence of alcohol, they are further required to remember what they wrote on the spreadsheet as waiters are floating around asking "Salmon?", "Nut roast?", "Turkey anyone?").

Imagine we had a spreadsheet where the staff team had to choose from items 1, 2 and 3. This is about the worst way of constructing this spreadsheet I can imagine but let's just run with it. You can see the world has changed and we've now got a horribly embedded If formula.

Of course there are much better ways of solving this problem. Index springs to mind (#2 on my Excel top 10) as does Choose (#4).

Let's pretend for a moment though that the world has shifted. For some reason we have no option but a horribly embedded If formula and we've even run out of rows or columns in our spreadsheet to break that complex logic down into its component steps.

The simplified If formula simplifies the logic

Have a look at this version of the embedded If formula. It does the same job but it's simplified over the first embedded function and perhaps a bit clearer. That's what the shorter-form If formula can do for you.

In each case I've taken the If logic test and multiplied by the number I am interested in.

You're a pro: there's no need ever to use a full If formula again

Taking the If logic test and multiplying by a number really is a good habit to get into. It's one of the hallmarks of an experienced modeller and simplifies your formulas. There really is no need ever to write out a full If formula again (when you're processing numbers in the form we have had today) and we've even less of a reason than ever to put one If formula inside another.