IF errors in Financial Models • AuditExcel.co.za

IF errors in financial models are a common finding from our financial model reviews. Below are ideas on easy ways of finding the IF errors in financial models, in order of riskiness, when auditing an Excel based financial model.

IF is a very popular function, unfortunately too popular. In many cases a simpler function would do the same thing but the user knows IF well and is not sure about the other function. It is also very difficult to review. Have a look at how we explain how you can build an IF function to get ideas of ways of reviewing complex IF functions by drawing a decision tree diagram.

It is useful to have a Financial Model error finding add in to find all the risky formulas but if you don’t have one you can do a manual search using the FIND tool.

Using “TRUE” or “FALSE” instead of TRUE or FALSE
Multi Nested IF functions
Complex IF formula
Logical Tests e.g. >0 or >=0?
YouTube Find all the IF Financial Model errors
Related

Using “TRUE” or “FALSE” instead of TRUE or FALSE

TRUE and FALSE are often used within IF functions. There is a user risk when it comes to these. In all other cases, when you enter a word into Excel, you must have inverted commas around it e.g. “OVERTIME”. However, TRUE and FALSE are different. Although the users type in a word, Excel sees it as a 1 (True) or 0 (False). If a user accidently enters it as “TRUE”, Excel will assume you are looking for the word TRUE and not the logical result True.

In this case, instead of searching for all the IF’s in the formula report listing, rather use the ‘Formula with Text’ and search for the ‘text’ “TRUE” and “FALSE”. If they are in an IF function there is a good chance it is an error. As shown below we can easily see all these instances and investigate immediately.

Multi Nested IF functions

IF is often used as a replacement for other formula which users may not be aware of. A common one is where a multi nested IF is used to work through some sort of sliding scale. As shown below, this is a fairly complex IF formula. In reality it would be better built with a VLOOKUP- true, but you have to review what you have been given. The risks include whether the logical test is correct (< or <=), whether the correct cells were referenced for each band, and whether a new band has been added to the sliding scale and incorrectly updated in the IF (or not updated at all).

Complex IF formula

As shown below, IF is often part of complex formula. In the normal financial model review you will cover these in detail, but it may be useful to spot check some of these combinations, especially when they involve some of the other risky functions we have highlighted. In the listing below we would look at any that have more than one IF (more about that elsewhere) and the ones that include some sort of error suppression like IFERROR or ISERROR.

Logical Tests e.g. >0 or >=0?

Financial model errors often happen around the border of a calculation. With IF functions it is often to do with the logical operator used especially with the bigger than and less than (> and <). Should the test be >100 or >=100. The problem will only be at that point but it is surprising how often these are the errors that slip through.

As shown below, we can use the Formula Report to list all the IF functions and then see which ones may need this type of investigation.