Microsoft Office Tutorials and References

In Depth Information

**Understanding information functions**

All the IS information functions take a single argument. For example, the ISBLANK function

takes the form =ISBLANK(
value
). The
value
argument is a reference to a cell. If
value
refers

to a blank cell, the function returns the logical value TRUE; otherwise, it returns FALSE. Note

that when you type numeric values as text, such as
="21"
, the IS functions, unlike other

functions, do not recognize them as numbers. Therefore, the formula =ISNUMBER("21")

returns FALSE.

TROUBLESHOOTING

My IS function returns unexpected results

Although you can use a cell range (rather than a single cell) as the argument to any IS

function, the result might not be what you expect. For example, you might think the

ISBLANK function returns TRUE if the referenced range is empty or FALSE if the range

contains any values. Instead, the function’s behavior depends on where the range is in

relation to the cell containing the formula. If the argument refers to a range that

intersects the row or column containing the formula, ISBLANK uses implicit intersection to

arrive at the result. In other words, the function looks at only one cell in the referenced

range and only if it happens to be in the same row or column as the cell containing the

function. The function ignores the rest of the range. If the range shares neither a row

nor a column with the formula, the result is always FALSE. For more about intersection,

see “Getting explicit about intersections” in Chapter 12.

An ISERR example

You can use ISERR to avoid getting error values as formula results. For example,

suppose you want to call attention to cells containing a particular character string, such as

12A, resulting in the word
Ye s
appearing in the cell containing the formula. If the string

isn’t found, you want the cell to remain empty. You can use the IF and FIND functions

to perform this task, but if the value isn’t found, you get a #VALUE! error rather than a

blank cell.

To solve this problem, add an ISERR function to the formula. The FIND function returns

the position at which a substring is found within a larger string. If the substring

isn’t there, FIND returns #VALUE!. The solution is to add an ISERR function, such as

=IF(ISERR(FIND("12A", A1)), " ", "Yes"). Because you’re not interested in the error, which

is simply a by-product of the calculation, this traps the error, leaving only the results in

which you are interested.