For 2024, I thought I would count down a very subjective “Top 12” of Excel function combinations. And this month’s offering makes it very clear that this is a highly subjective list. If you don’t like this month’s suggestion, that’s fine; life would be boring if we were all in agreement.

Continuing our Top 10 countdown, we are now in the home straight as we arrive at the Top 3. The 3rd of 12 is a very useful combination: IFERROR FORMULATEXT.

Let’s consider them individually to begin.

IFERROR first came into being back in Excel 2007. It was something users had continued to ask Microsoft for. At the time of writing, there are 13 IS functions, ie, functions that give rise to a TRUE or FALSE value depending upon whether a certain condition is met:

1. ISBLANK(reference): checks whether the reference is to an empty cell
2. ISERR(value): checks whether the value is an error (e.g. #REF!, #DIV/0!, #NULL!). This check specifically excludes #N/A
3. ISERROR(value): checks whether the value is an error (e.g. #N/A, #REF!, #DIV/0!, #NULL!). This is probably the most commonly used of these functions in financial modelling
4. ISEVEN(number): checks to see if the number is even
5. ISFORMULA(reference): checks to see whether the reference is to a cell containing a formula
6. ISLOGICAL(value): checks to see whether the value is a logical (TRUE or FALSE) value
7. ISNA(value): checks to see whether the value is #N/A. This gives us the rather crude identity ISERR + ISNA = ISERROR
8. ISNONTEXT(value): checks whether the value is not text (N.B. blank cells are not text)
9. ISNUMBER(value): checks whether the value is a number
10. ISODD(number): checks to see if the number is odd. Personally, I find the number 46 very odd, but Excel doesn’t
11. ISOMITTED(argument): checks to see whether an argument is missing based upon a LAMBDA function
12. ISREF(value): checks whether the value is a reference
13. ISTEXT(value): checks whether the value is text.

You get the idea. As mentioned previously, sometimes you need to trap errors that may originate from a formula that is correct most of the time. Where possible, you should be specific with regard to what you are checking, eg

=IF(Denominator=0, error_trap, Numerator/Denominator)

In this example, I am checking to see whether the Denominator is zero. I could use this formula instead:

=IF(ISERROR(Numerator/Denominator), error_trap, Numerator/Denominator)

The difference here is that this will check for anything that may give rise to an error:

Do you see the problem here? I have to put the same formula in twice. If that is a long formula, then the calculation becomes doubly long. This is where IFERROR comes in; it halves the length of the calculation but still achieves the same effect

=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, eg,

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.

Sometimes you have to use IF and ISERROR in combination anyway:

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

In this example, the formula is checking to see whether a particular calculation gives rise to an error. If it does, the error_trap will be referenced in the usual way, but if not a different_calculation (not the calculation used for the test) will be computed.

These two methodologies should be mastered. You will create more robust and flexible models once your error become a thing of the past. Not just the model – but your own expertise – will become more trusted in your organisation if users never encounter prima facie errors in your model.

New in Excel 2013, this is one of the most used functions in our neck of the woods. It’s a really useful tool for documenting formulae, as FORMULATEXT returns a formula as a text string. People have been writing User-Defined Functions (UDFs) for years to replicate that!

The FORMULATEXT function employs the following syntax to operate:

FORMULATEXT(reference)

The FORMULATEXT function has the following argument:

• reference: this is required and represents a cell or a reference to a range of cells. 

The FORMULATEXT function returns what is displayed in the Formula bar if you select the referenced cell, where the reference argument can be to another worksheet or workbook.

FORMULATEXT returns the #N/A error value if:

• the cell used as the reference argument does not contain a formula
• the formula in the cell is longer than 8,192 characters
• the formula cannot be displayed in the worksheet; for example, due to worksheet protection
• an external workbook that contains the formula is not open in Excel.

Interestingly, entering a reference to the cell in which you are entering the function as the argument will not result in a circular reference warning. FORMULATEXT will successfully return the formula as text in the cell. Indeed, this observation gives rise to one of my favourite Excel examples:

The IFERROR FORMULATEXT results in the formula for a given cell, but provides an error_trap should FORMULATEXT not be recognised (ie, the version of Excel used is earlier than Excel 2013), or else the formula is too long, inaccessible or refers to a cell that does not contain a formula, eg,

Here, cells B3:B5 contain the formulae cited in the corresponding column D cells. However, cells D6 and D7 return the error_trap, “Error”, since B6 is simply cell B5 copied and pasted as a value (so not a formula) and cell B7 simply had the value ‘5’ typed in. Simple!

As older versions of Excel are consigned to oblivion, this function combination will no longer be necessary for versions of Excel where FORMULATEXT was not recognised (prior to Excel 2013). However, FORMULATEXT may still give rise to other types of error, so this combination will remain useful and used.

The top 3 continues next month.