Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula. Use the IFERROR function to trap and handle errors in a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).).
Syntax
IFERROR(value,value_if_error)
Value is the argument that is checked for an error.
Value_if_error is the value to return if the formula evaluates to an error. The following error types are evaluated: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.
Remarks
- If value or value_if_error is an empty cell, IFERROR treats it as an empty string value ("").
- If value is an array formula, IFERROR returns an array of results for each cell in the range specified in value. See the second example below.
Example: Trapping division errors by using a regular formula
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
| A |
B |
| Quota |
Units Sold |
| 210 |
35 |
| 55 |
0 |
| |
23 |
| Formula |
Description (result) |
| =IFERROR(A2/B2, "Error in calculation") |
Checks for an error in the formula in the first argument (divide 210 by 35), finds no error, and then returns the results of the formula (6) |
| =IFERROR(A3/B3, "Error in calculation") |
Checks for an error in the formula in the first argument (divide 55 by 0), finds a division by 0 error, and then returns value_if_error (Error in calculation) |
| =IFERROR(A4/B4, "Error in calculation") |
Checks for an error in the formula in the first argument (divide "" by 23), finds no error, and then returns the results of the formula (0). |
|
Example: Trapping division errors by using an array formula
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|
|
| A |
B |
C |
| Quota |
Units Sold |
Ratio |
| 210 |
35 |
=IFERROR(A2:A4/B2:B4, "Error in calculation") |
| 55 |
0 |
|
| |
23 |
|
| Formula |
Description (result) |
|
| =C2 |
Checks for an error in the formula in the first argument in the first element of the array (A2/B2 or divide 210 by 35), finds no error, and then returns the result of the formula (6) |
|
| =C3 |
Checks for an error in the formula in the first argument in the second element of the array (A3/B3 or divide 55 by 0), finds a division by 0 error, and then returns value_if_error (Error in calculation) |
|
| =C4 |
Checks for an error in the formula in the first argument in the third element of the array (A4/B4 or divide "" by 23), finds no error, and then returns the result of the formula (0) |
|
|
Note The formula in the example must be entered as an array formula. After copying the example to a blank worksheet, select the range C2:C4, press F2, and then press CTRL+SHIFT+ENTER.
