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

advantage.

ISBLANK, ISNONTEXT, ISTEXT,

and ISNUMBER

The ISBLANK, ISNONTEXT, ISTEXT, and ISNUMBER functions tell you what

type of data are in a cell.