Remove errors in Excel with IFERROR and IFNA functions

     How to remove  #VALUE!, #N/A, #NAME?, #REF!, #NUM!, #DIV/0!, and #NULL! errors in Excel with IFERROR and IFNA functions

      Both of the functions - IFERROR and IFNA - are used in Excel to trap errors in formulas. And both functions can return a special value that you specify if a formula produces an error. Otherwise, the result of the formula is returned.

     The difference is that IFERROR handles all possible Excel errors, including #VALUE!, #N/A, #NAME?, #REF!, #NUM!, #DIV/0!, and #NULL!. While the Excel IFNA function specializes solely in #N/A errors, exactly as its name suggests.

      The syntax of the Error functions is as follows.

     IFERROR function

IFERROR(value, value_if_error)

    IFNA function

IFNA(value, value_if_na)

    The first parameter (value) is the argument that is checked for an error.

     The second parameter (value_if_error / value_if_na) is the value to return if the formula evaluates to an error (any error in case of IFERROR; the #N/A error in case of IFNA).

    Note. If any of the arguments is an empty cell, both of the Error functions treat it as an empty string ("").

     The following example demonstrates the simplest usage of the IFERROR function:

=IFERROR(B2/C2, "Sorry, an error has occurred")

     As you see in the screenshot above, column D displays the quotient of the division of a value in column B by a value in column C. You can also see two error messages in cells D2 and D5 because everyone knows that you cannot divide a number by zero.

     In some cases, you'd better use the IF function to prevent an error then ISERROR or ISNA to catch an error. Firstly, it's a faster way (in terms of CPU) and secondly it is a good programming practice. For example, the following IF formula produces the same result as the IFERROR function demonstrated above:

=IF(C2=0, "Sorry, an error has occurred", B2/C2)