Excel true false checks

Experienced financial modelling practitioners know that Excel True/ False checks (as well as Max and Min functions) can be used as a shorter neater alternative to If functions. If there’s a prize in financial modelling of course it should be for finding the shortest neatest solution to a problem, and we do have some neat alternatives to the standard If functions. You’ll see advanced modellers using those.

Constructing True/ False checks

True/ False checks are very easy to construct in an Excel financial model. They can be used for example to check that a balance sheet is balancing, debt is paid down, financial ratios are met or check that other key outputs of your financial model are within a particular range. In the True/ False checks download example attached you can see a simple True/ False check being used at line 8 to check that a model is producing no balance sheet error. All that’s entered in the cell is a simple function in the form “=[Target]=[Value]”. In column D the Target is zero and the actual value produced by the model is 0.3. The check produces “FALSE” and highlights an error. This check is a bit quicker and easier to construct than the alternative at line 7 which uses an If function.

Image showing how to produce True/ False error checks in Excel financial modelling

TRUE = 1, FALSE = 0

When you see “TRUE” in an Excel cell, you think you are reading text but Excel sees TRUE as being the same as the value of 1. FALSE is the same as zero.

Shortcutting If functions: an example

With FALSE = 0, multiplying any cell in your model by a FALSE (= zero) check result will result in a value of zero in your model. This is often the kind of problem you might otherwise wish to solve using an If function (unless a certain condition is met, you want nothing appearing in your model). So constructing a True/ False check and then multiplying through by the check can sometimes shortcut an If function. Creating a separate line containing the True/ False check can also make it clearer to a new user which parts of a model are switched on/ off.

Image showing how to construct a shortcut IF function in Excel

Once you’ve discovered, as above, that you can simply multiply a number by a TRUE/ FALSE logic test, you may pledge never to use a full IF function again (when manipulating values). If you’ve made that promise pat yourself on the back: you inhabit the dwelling place of properly-advanced financial modellers!

Next in the Excel financial modelling course extract

Coming up in the next lesson we look at using Excel data validation to drive users to enter particular values in a financial model.

About this online financial modelling course material

You’re looking at a free online financial modelling course extract covering Excel functions.



...join the conversation...

Your email address will not be published. Required fields are marked *