We know that a well-designed financial model contains in-built, automatically calculating error checks and a good financial modeller is always looking for opportunities to build error checks into their model.
Join financial modeller, Danielle Stein Fairhurst for this free live webinar filled with practical tips and tricks for creating simple as well as more complex error checks in your everyday financial models.
Throughout the webinar we cover:
- Which errors need checking?
- Which Excel error values not to use.
- Why you should love your error values!
- Types of model error.
- Excel Horror Stories.
- Strategies for reducing error.
- Sense-checking cognitive bias.
- Using error checks.
- Circular References.
- Tips for finding errors.
This webinar was held on Thursday 15th September. The webinar has now ended but replay and files are now available.
Error values such as #REF!, #NA! or #VALUE! are Excel’s irritating but necessary way of telling us that something’s not quite right with your formula or function. Although they can be frustrating and time-consuming to rectify, dealing with them is an obligatory part of modelling. Instead of being ignored, supressed or yelled at, Excel errors should be heeded, taken care of and according to a recent article, perhaps even celebrated!
Excel errors should not be confused with error checks, which are formulas created by the modeller that have been designed to alert the user as soon as the model is not calculating correctly. Excel error values are errors displayed by Excel when the formula has not been built properly. For example, using the multiplication symbol “x” instead of “*” e.g. =I36xJ36 is an incorrect formula which will return the value #NAME? because Excel does not recognise it. Other common sources of error are brackets (parentheses) that do not match, or missing sections for functions.
If you get an Excel error message, click OK on the error message’s dialogue box. Then either edit the formula on the Formula Bar to correct the formula, or click the Cancel button to clear it.
Here are some common error values you might get, and how to fix them:
######
The railroad tracks; my favourite! This is not really an error, and very easily to fix. The result is too long to fit in the cell, so just make the column wider.
#DIV/0!
You’re trying to divide by zero. Check that the formula is dividing by is not empty, as this will translate to a zero value.
#NAME?
There’s a name in the formula Excel doesn’t recognise. You may have named a range, and then spelt it incorrectly, or typed “VLOOKKUP” instead of “VLOOKUP”.
If you used a name you defined, check its spelling. You can avoid this error by selecting a name in the Name Box instead of typing it in.
If you typed in a function, check its spelling or verify that such a function exists.
If you are performing operations on text, make sure you have enclosed it in double quotation marks.
#REF!
Your formula refers to a cell that no longer exists, due to a change in the worksheet. This happens if you deleted cells referred to in the formula or pasted moved cells on cells referred to in the formula.
This is probably one of the more difficult errors to fix because you no longer have access to the cell that was deleted. You will need to re-enter the cell reference which is missing.
#VALUE!
The formula is using a different type of reference to what the function was expecting. The most common cause of this is if a user tries to enter text into a cell instead of a number, or the formula is referring to a label instead of the numeric value. For example: “TBA” or “Less than 10” instead of a numerical value. Check to see that some fields are not referring to cells containing text.
#N/A
This means that there is some data missing; the value the formula looking for is not available. A common reason for receiving this error is if a LOOKUP cannot find the criteria it is searching for in the source data. Make sure that the
function can find the data you’ve asked it to look for.
With the exception of the #### railroad tracks, all of these error values can be suppressed using the IFERROR function which will stop the error from showing. So, for example, if the cell A2 contained a zero, the formula =IFERROR(A1/A2,0) will return a zero instead of #DIV/0!. Be careful, of course that you don’t suppress a real error which should be shown, and fixed!
