Occasionally, Excel will return an error with functions and formulas. This might happen when you try to perform math functions on text, have an incorrect lookup reference, or thousands of other reasons. These errors are shows as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!. Typically when you get an error, you should go in and fix your formula. There are instances, however, when you simply want to ignore an error. When that happens, the IFERROR function is extremely helpful.
IFERROR simply allows you to return a value if an error occurs. The function requires two things:
Value: This is the formula that is checked for an error.
Value_if_error: The value that you want to return if the formula evaluates an error.
Example
Let’s say that you have a report from your payroll system that shows overtime hours worked during the past week. If an employee doesn’t work any hours, it returns the value “N/A.” You want to quickly calculate total overtime pay and pull the formula down all the rows.
If you simply multiply the Overtime Rate column and the Overtime Hours column, any instance of “N/A” in the hours column will produce an error:
Because you want to pull this formula down and not manually delete any errors, you can use the IFERROR function to help out.
In this case, the Value would be the formula multiplying the Overtime Rate and Overtime Hours. For the Value_if_error, you could simply put a 0. You could also type a description such as, “No Overtime.” To clearly show those employees who did not have any overtime that week.
Pulling the formula down then gives you a clean column with no errors.
