Please: no more Jedi stuff

This, I'm going to try and promise, is the end of the Jedi stuff. I don't mind people thinking I'm an Excel nerd but being a Star Wars nerd could be a step too far, even for me.

For those who have been following the 'Jedi' series of posts, these are the main points:

• If functions make it to the top of my top 10 list of all time great Excel formulas
• Usefully the logic test that sits inside an If function stands alone. When you're trying to manipulate numbers you need never write out a full If function again. In fact, ignoring an opportunity to simplify your functions makes you look like a rank amateur. See: an If function masterclass to simplify your If formulas for life
• Max and Min functions can be another useful shortcut for standard If functions (sooo many reasons to do something cleverer than a standard If function)
• You will regularly want to use "And" as well as "Or" inside your If functions, but you can be a Jedi with those too: more Jedi If functions.

Other ways to use the logic test

This final post in the series makes the point that the stand alone logic test has a few other regular uses apart from allowing you to shorten If functions like a pro. You can:

• Use the logic test to perform simple checks (it's not rocket science that one)
• Use the logic test to create switches that switch sections of your model on or 'phase' or 'slide' numbers.

The humble logic test is a key tool in building flexibility into your modelling.

The logic test for simple checks

This one's a simple point that may have been missed amongst your new-found enthusiasm for the idea that you can model like a Jedi.

Imagine you were performing some kind of check looking at one number against another e.g. that the model balance sheet error (say zero) matches what you expect it should (zero). You want a trigger if the output is anything other than zero.

Before becoming a Jedi you probably would have thought of doing something like =IF([The balance sheet error]=0,"OK","Error").

Now you're a Jedi you know that the logic test in the first part of the If function stands alone. So you're probably in the habit of, in a flash, doing something like [The cell that has the balance sheet error in it]=0. And if you don't like seeing the words "TRUE" or "FALSE" splashed all the way across your model you could tidy things up a bit by doing something like ([The balance sheet error]=0)*1.

This is not a big one. Jedis construct model checks. Jedis construct model checks really quickly. Jedis don't usually bother writing out a full If statement to check that one result number matches an expected target number. Why would they?

Building in switching and flexibility

You've really got the hang of this now. You're used to 1s and 0s appearing across your model to check that results match expectation. 1 is good. 0 is not. Now you want to make your model flexible. Imagine you wanted to incorporate project delay, e.g. capital expenditure could be delayed by a certain number of months. You want to 'slide' or push cap-ex out by the delay. The last thing you would ever think of doing is building some horrendous embedded If function to handle that.

Step 1: create a separate switch

Step 1 to incorporating flexibility is to create a new line that contains a logic switch that's going to help. It's a separate line because the switch itself could get a bit complicated and you want to make it clear what's going on (no embedding). It's a separate line because cap-ex might not be the only item that will get delayed in the same manner. Revenues could get delayed in the same way. So could operating expenses. The first step is to create a stand-alone logic switch. You might even put it at the top of your model if it's going to be used regularly in elsewhere.

Step 2: use the switch to lag cap-ex

Make use of the switch. Now you've got your chance to add a lag to cap-ex (or operating revenue, or expenses, or whatever else you are thinking of dragging forward or back in your model).

It's pretty much the same principle that we discovered when we first looked into Jedi If functions where we saw the logic test as its own beast with its own use.

Note the use of Index which comes it at #2 in the top 10 (instead of the 'volatile' Offset, which in a pre-Jedi life could have been your temptation).

Three cheers for?If's humble logic test!

Once you really discover that seemingly humble logic test sitting inside an If function you'll start seeing it as something that has its own special power. That logic test stands alone and you can use it every day to:

• Shorten your If functions ([logic test] x [number])
• Create simple checks super fast ([logic test] x 1)
• Build flexibility into your modelling and be super clear about how you're doing that.

If's logic test rocks! Now, no more Jedi-talk. I promise.