Performing error checks in an Excel financial model is an important step. Various functions are available to check and handle errors such as functions that check for all errors, check exclusively for one error type, and functions that ignore a certain error type. In this article, we will discuss the last function, specifically the ISERR function in Excel.

What is the ISERR Function in Excel? How to use the Excel ISERR formula? Why does the ISERR Excel function exclude #NA error? This article will answer these questions and provide examples of how ISERR Function works.

What is The ISERR Function in Excel

The ISERR Function in Excel is under the Information Functions and tests for any errors except #NA error and results in TRUE/FALSE values.  The Excel ISERR formula works with errors such as #REF, #VALUE, #DIV/0, #NUM, etc.

ISERR Excel Formula Syntax

Where:

Value
– Cell reference, formula, or expression to be tested for any error. The ISERR Excel function excludes the #NA error. (Required Argument)
– When an error other than #NA error is identified, the ISERR Function returns TRUE.
– When no error or #NA error is identified, the ISERR Function returns FALSE.

ISERR Function in Excel Examples

Why ISERR Function Excludes #NA Error?

The #NA error is an abbreviation for Not Available or No Value Available and mainly occurs when a formula or function cannot find the value it was instructed to search and retrieve.

The #NA error commonly appears when the lookup value using VLOOKUP, HLOOKUP, MATCH, and INDEX and other lookup functions or combinations cannot be found. This does not necessarily mean there is a real error or there is something wrong with the function (false positive). It could be the lookup value is not valid and is correctly excluded from the list.

When a financial model contains lookup values, performing an error check with the ISERR Function is a suitable tool. Since the ISERR Excel formula ignores the #NA error, the financial model provides a more realistic error check. Using a stricter error check function such as ISERROR or IFERROR will lead to incorrectly identifying and removing #NA error in the financial model.

IF and ISERR Function in Excel Combined

The Excel ISERR formula is a helpful tool in testing and identifying error values. The ISERR Function then provides a simple and short result by returning TRUE/FALSE values. However, this can be limiting as it may not necessarily give more information to the end-user. A clearer, specific, and customized error message in a financial model is highly preferred.

How to return this kind of error message with ISERR Function in Excel? By combining the IF Function and ISERR Excel Function.

Recall the IF function is a simple Logical function that:

1. Evaluates or tests data, formula, or expression based on given criteria (Logical test)
2. Depending on the outcome of the test,  it performs a specific course of action otherwise an alternative course of action (Value if TRUE and Value if FALSE)

Applying the IF and ISERR Excel Function combined with the previous error examples, provides a clearer error message.

The flow of the IF and ISERR Excel functions combined.

When the combination of IF and ISERR Excel functions are applied to test the #NA error similar to the example below.

The IF and ISERR Excel functions return the value in cell B6, #NA error. Why? Since the ISERR Excel disregards the #NA error (FALSE), the IF function then returns the Value_if_False, cell B6.

Major Points to Remember Using Excel’s ISERR Function

• Incorrectly handling a true error in a financial model can lead to wrong decisions, it is wise to use Excel’s error-checking tools or create error checks specific to the financial model.
• ISERR Function in Excel tests for errors except for #NA error and returns TRUE/FALSE values.
• Combine IF and ISERR Excel functions to return error messages other than TRUE/FALSE.
• Since the ISERR Function ignores #NA error, use ISERROR and IFERROR functions instead to check all types of errors.
• To test #NA error only and ignore other errors, use ISNA or IFNA functions in Excel.