If you make a mistake in writing a formula in Excel there are a number of pre-set error messages which come up.
These can be helpful in letting you know what is wrong with the formula, but are generic and not very user friendly.
There is a function in Excel called IFERROR which lets you create custom error outputs instead of displaying the default error message.
Download File
If you would like to follow along download the attachment below.
 How IFERROR works
There are default errors that Excel displays if a formula or function calculates an error, including:
· '#DIV/0!': Division by zero error
· '#N/A': Value not available error
· '#NAME?': Name error (e.g. misspelled function names)
· '#NULL!': Null error (e.g. incorrect range references)
· '#NUM!': Number error (e.g. invalid numeric values)
· '#REF!': Reference error (e.g. invalid cell references)
· '#VALUE!': Value error (e.g. wrong type of argument in a function)
IFERROR changes the error output from one of the above defaults to an output of your choice.
The IFERROR function has only two entry requirements, value and value if error.
·       Value: This is the formula or function you want to evaluate
·       Value_if_error: This is the value or action you want to take if the formula or function results in an error
Using IFERROR To Provide a Numeric Output
A common example of this would be when you divide a number by zero, in Excel this returns the '#DIV/0' error.
If you enter the formula =50/0 you would get this error as Excel cannot divide a number by a zero.
To customise your own error output for this you can use IFERROR along with the formula:
Writing the formula in an IFERROR where the value_if_error is 0 means the output will now be 0 instead of '#DIV/0!' which is the default error Excel would show.
Using IFERROR with Cell References
This also works with cell referencing, so if you had the value 50 in cell A2, and 0 in B2 you could use IFERROR in the following way:
Using IFERROR To Provide a Text Output
You can also have a text output instead of a number, but the text would need to be in quotation marks.
If you do not use quotation marks Excel will return the '#NAME?' error.
Using IFERROR With a Function
As well as formulas you can use functions such as SUMIFS or VLOOKUP in the value section of an IFERROR.
When using a VLOOKUP to get a value from a dataset you are required to enter a lookup value. If that value is in the dataset it will find it and pull through the relevant value, if the value is not in the dataset then as a default the VLOOKUP with return the '#N/A' error.
If you have a large dataset or if someone is viewing it for the first time '#N/A' does not give a lot of information about the error.
IFERROR allows you to add a custom error message that make the issue clearer for users.
Conclusion
By default if you enter an incorrect formula or function Excel will give pre-set errors such as '#DIV/0!' or '#N/A'.
These can be generic or confusing as they do not provide a lot of information about the specific issue that has led to the error.
Using the IFERROR function you can choose what output you want to see if the formula or function you have entered returns an error.
If you want your error to be a number then you just need to enter a number in the value_if_error section, but if you want to have text as an output then you need to make sure that the text you write in the value_if_error section is in quotation marks or you will get the '#NAME?' error.
Customising your errors can help stop calculations breaking and make it clearer to users what the issue is with the incorrect formula or function.
Comments