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

Custom Search