Microsoft Office Tutorials and References
In Depth Information
Finding What IS and What IS Not
Why is #N/A treated separately? It is excluded from being handled with ISERR
and has its own ISNA function. Actually, you can use #N/A to your advantage
to avoid errors. How so? Figure 15-4 shows an example that calculates the
percentage of surveys returned for some of Florida’s larger cities. The
calculation is simple — just divide the returned number by the number sent.
However, errors do creep in. (Creepy errors, yuck!) For example, no
surveys were sent to Gainesville, yet 99 came back. Interesting! The calculation
becomes a division by zero error, which makes sense. On the other hand,
Tallahassee had no surveys sent, but here the returned value is the #N/A
error, purposely entered. Next, look at Column E. In this column, True or
False is returned to indicate whether the calculation, per city, should be
considered an error — Gainesville true, Tallahassee false.
true or false appears in Column E because all the cells in Column E use
the ISERR function. The formula in cell E13, which tests the calculation for
Tallahassee, is =ISERR(D13).
Simply put, D13 displays the #N/A error because its calculation (=C13/B13)
uses a cell with an entered #N/A. The ISERR does not consider #N/A to be an
error; therefore, E13 returns False. The upshot to all this is that eyeballing
Column E makes distinguishing entry and math errors from purposeful
flagging of certain rows as having incomplete data easy.
Figure 15-4:
Putting an
error to your