The ‘IFERROR’ function in Excel will return a set value (which you select) when a formula generates an error. If no formula error is returned, then the normal formula output is shown.
The IFERROR function will take effect if any of the following errors occur; #N/A, #REF!, #VALUE!, #NUM!, #DIV/0!, #NAME?, or #NULL!.
When dealing with large data sets where you are copying formulas across numerous rows (e.g. if calculating the gross profit margin for a number of products which may or may not have sales in each period), it can be useful to overlay the IFERROR formula to avoid seeing numerous #DIV/0 errors.
Types of Excel errors:
- #N/A – The #N/A error is returned when the numbers you are referring to within your formula cannot be found. For instance, if the cell you are trying to lookup in your =VLOOKUP formula cannot be found within the specified cell range, an #N/A error is returned.
- #REF! – The #REF! error is returned where you are referencing a cell that doesn’t exist. For instance, the formula =A1+B2 would return a #REF! error if column B was subsequently deleted.
- #VALUE! – The #VALUE! error is returned when it finds text or other characters where it expects to find numbers. For instance, a value error would be returned if you tried to add separate cells containing 7 + G (or any other letter).
- #NUM! – The #NUM! error arises when there is a problem with a number in the formula. For instance, if you apply =LARGE() to a cell range with 4 numerical cells, and ask for the fifth largest number, a #NUM! error will be returned as the fifth largest number does not exist.
- #DIV/0 – The #DIV/0 error is returned if you have attempted to divide by zero.
- #NAME? – The #NAME? error is returned if you have attempted to refer to an incorrect named range.
- #NULL! – The #NULL error is less common as it typically only occurs where you specify an intersection of two areas which do not actually intercept.
- Value – The cell reference or formula you want to check for an error.
- Value_if_error – The value you want to return in the event of an error. If you do not wish for a value to be returned, enter “”.
A set value (which you select) where a formula generates an error.