Microsoft Office Tutorials and References

In Depth Information

**Using the IFERROR or IFNA Function to Simplify Error Checking**

For example, suppose you need to find all flights landing outside Oklahoma.

You can build a massive OR statement to find every airport code in the

United States. Alternatively, you can build an OR function to find Tulsa

and Oklahoma City and then use a NOT function to reverse the result:

=NOT(OR(A2="Tulsa",A2="Oklahoma City")).

Using the

Using the
IFERROR

IFERROR
oor
IFNA

IFNA
Function to Simplify Error Checking

Function to Simplify Error Checking

The IFERROR function, which was introduced in Excel 2007, was added at the

request of many customers. To better understand the IFERROR function, you

need to understand how error checking was performed during the 22 years be-

fore Excel 2007 was released.

Figure 12.7
shows a typical spreadsheet that calculates a ratio of sales

to hours. Even though this formula works most of the time, in occasional re-

cords, the divisor is zero, and the formula returns a #DIV/0 error.

Figure 12.7.

Figure 12.7.
The zero in the divisor in row 5 causes a division-by-zero error.

The zero in the divisor in row 5 causes a division-by-zero error.

The typical way to deal with this in legacy versions of Excel was to set up

an IF function to check whether the divisor was zero: =IF(C5=0,0,B5/C5). If

the divisor was zero, the formula returned a zero as the result. Otherwise,

the formula performed the calculation.

In legacy versions of Excel, it was typical to use this type of IF formula on

thousands of rows of data. The formula is more complex and takes longer to