Handling Formula Errors in Excel

Confucius says: there is more than IFERROR in error handling

Admit it or not, errors in formulas are a regular companion for everyone who uses formulas that are a bit more complicated than the simple subtraction and addition. There are two general types of error we make in spreadsheets - logical and formula errors. Producing error-free sheets and models is a huge topic and I will be covering it in this blog. Today I would like to focus on one the most popular ways to handle formula errors.



Error handling is not a must of course and depends on many factors such as the purpose of the sheet, requirements, what the client wants to see, personal preferences of the sheet developer, data limitations, etc. You could live happily with some sheets having lots of #DIV/0! and #VALUE! on them but is some cases this could be detrimental for the model success, not to mention the problems when the formula result is a link in a chain of calculations.


The Popular Way
The formula errors are #NULL!, #DIV/0!, #VALUE!,#REF!,#NAME?, #NUM! and #N/A. A very popular way to handle them is by using the IFERROR function (see definition). It is very simple and easy to remember and implement. However, simply enclosing formulas with IFERROR could bring some unpleasant surprises.


What Could Go Wrong
The problem with IFERROR is that it treats all the errors that may occur in the same way. If you had in mind preventing the #DIV/0! and type IFERROR(А1,0) then you will get zero no matter what is the actual error. You will not see and handle properly mistakes that indicate other problems with the formula in cell A1. You could miss #N/A that would indicate a value is not found in a look-up or #NAME to indicate a typo in the formula name or #REF for referring a cell that has been deleted or any other problem with your calculation. Some of these errors could be spotted easily but some could lurk into your calculations and could be difficult to identify.


Two Solutions
One  way to avoid this is by using ERR.TYPE function (see definition). It returns an error number, e.g  #DIV/0 is number 2, #NUM! is 6, #REF! is 4 and so on (please refer to the link to see the full list). You can embed this formula in a regular IF function to handle an error in a way you like and raise others that you would like to see. For example, you can have

= IF(ERR.TYPE(A1)=2, "",A1)

This formula will return an empty string if A1 returns #DIV/0. Otherwise it will return the formula result or any other error that it may have, e.g. 1 (if the result is 1) or #REF! if it refers to a deleted cell. You could write an IF formula to treat the different errors in a different way. Remember that Excel cannot nest more than seven IF functions (there is workaround for it but this is a different discussion). However, in reality we do not need to handle all the possible errors and this is not a major concern.
This approach is neat but it could bring some other problems apart from the limitation on nested IFs. Probably the most important one comes from the need of writing long IF formulas - difficult to write, difficult to trace and even more difficulties come when model support has to be transferred to another party. The other problem is this formula is it could impact the performance of your spreadsheet if it is applied on a large area through the fact that it has to be part of an IF formula.

The other approach I use is based on the way of work. I see three phases when working on a spreadsheet - development, test and delivery. I start with constructing the spreadsheets and calculation flow in the development phase and I do not implement formula error handling at all. This allows me to easily see and fix errors. Once all calculations are set and all functionality implemented I start the test phase where I check spreadsheet behavior for the ranges of data, input parameters, etc and see what the errors are. Some of the occurring error have to be fixed as they are a result of a mistake. Some cannot be fixed as they are linked to data availability or some other external problem. For example, a look-up function for sales to a customer in Jan returns an error as there are none - everything is OK with the calculations but the data for this month is simply not available. I apply the error handling on the third phase - the delivery to the customer. In this phase I know what are the potential problems and I apply the appropriate error handling - as in the example with the sales to a customer, I set look-up to return zero or handle other types of errors caused by the nature of the calculations. Some cases allow not to apply error handling but simply to hide the error. One way is to include additional error handling in the formulas to cover these cases. Another is to use conditional formatting - it uses some error-checking formula but is sometimes easier to handle than including additional check in cell formulas.The downside of this approach comes from the risk input data to have values that have not been detected or foreseen.


There are probably other ways to handle formula errors. Application of each of them should be flexible to the specific spreadsheet, its purpose, time limitations and other project details. The thing to remember is we have to prepare for the errors and be aware of the advantages and downsides of the approach we implement.