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:
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. 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