Top 12 Excel Combinations: 11. IF ISERROR (Error Trapping)

Second in my series and coming in 11^th of 12, is a combination which is highly useful but is often superseded these days, hence its lower ranking.  Let me introduce IF ISERROR into the fold.

I explained the IF function last month:

=IF(logical_test, [value_if_TRUE], [value_if_FALSE])

This function has three [3] arguments:

• logical_test: this is the “decider”, that is, a test that results in a value of either TRUE or FALSE.  Strictly speaking, the logical_test tests whether something is TRUE; if not, it is FALSE
• value_if_TRUE: what to do if the logical_test is TRUE.  Note that you do not put square brackets around this argument.  This is just the Excel syntax for saying sometimes this argument is optional.  If this argument is indeed omitted, this argument will have a default value of TRUE
• value_if_FALSE: what to do if the logical_test is FALSE (not TRUE).  If this argument is left blank, this argument will have a default value of FALSE.

Its partner in crime, the ISERROR(value) function, is one of the IS family of functions and checks whether the value is an error (e.g. #REF!, #DIV/0!, #NULL!).  This used to be one of the most commonly used IS functions in financial modelling as it was used for the concept of error trapping.  For example, consider the following:

Do you see what the problem used to be here?  You had to put the same formula in twice.  If that was a long formula, then the calculation became doubly long.  This is where IFERROR (introduced in Excel 2007) came in; it halved the length of the calculation but still achieved the same effect, viz.

=IFERROR(calculation, error_trap)

Essentially, this formula is the illegitimate lovechild of IF and ISERROR.  It checks to see whether the calculation will give rise to a prima facie error.  If it does, it will return error_trap; otherwise, it will perform the said calculation, e.g.

You shouldn’t just sprinkle IFERROR throughout your models like your formulae are confetti.  Used unwisely, IFERROR can disguise the fact that your formula isn’t working correctly and that modifications to the logic may be required.  Try to use it sparingly.

IF and ISERROR in combination are still used upon occasion:

=IF(ISERROR(calculation), error_trap, different_calculation)

Let me provide a very common example.  Imagine I have created a model where someone has deleted a key Excel formula:

In the example, the reference in cell C5 no longer exists giving rise to an #REF! error.  Unfortunately, this does happen in models.  Even if you protect a worksheet (ALT + T + P + P), the end user may still delete the sheet!  (Protecting the workbook – ALT + T + P + W – will prevent this, but the workbook can still be deleted.)

Therefore, if someone does manage to accidentally delete a key reference, I would want an error check to alert me accordingly.  Further,  I would prefer to be alerted using a 1 / 0 (Boolean) system where:

• 1 denotes there is an error
• 0 denotes there is no error.

This way the total number of errors may be added easily.

A simple formula here would be as follows:

This check,

=IF(ISERROR(C6-C10),1,)

provides the value one [1] if Net Assets less Total Equity may not be evaluated. This is not the same as the formula:

=IFERROR(C6-C10,1)

Whilst this formula will provide a value of one [1] if the subtraction cannot be evaluated, the alternative is not necessarily zero [0].  This formula is not intended to be my balance check, merely a check to ensure that my balance check will work.  If I were to use IFERROR rather than IF(ISERROR) the values could be anything.  I want values of zero and one only.  Therefore, there is still a need for IF ISERROR, although its usage is slowly but surely diminishing.

The Top 10 of Excel Function Combos starts next month…