Microsoft Office Tutorials and References
In Depth Information
Understanding information functions
Understanding information functions
The information functions can be considered the internal monitoring system in Excel.
Although they perform no specific calculations, you can use them to find out about
elements of the Excel interface and then use that information elsewhere. We discuss the
most useful of these functions in the following sections. You can find these functions by
clicking the More Functions button on the Formulas tab on the ribbon and then clicking
Information.
Using selected information functions
With information functions, you can gather information about the contents of cells, their
formatting, and the computing environment and also perform conditional tests for the
presence of specific types of values.
The TYPE and ERROR.TYPE functions
The TYPE function determines whether a cell contains text, a number, a logical value, an
array, or an error value. The result is a code for the type of entry in the referenced cell: 1 for
a number (or a blank cell), 2 for text, 4 for a logical value (TRUE or FALSE), 16 for an error
value, and 64 for an array. For example, if cell A1 contains the number 100 , the formula
=TYPE(A1) returns 1 . If A1 contains the text Microsoft Excel, the formula returns 2 .
Like the TYPE function, the ERROR.TYPE function detects the contents of a cell, except it
detects different types of error values. The result is a code for the type of error value in the
referenced cell: 1 for #NULL!, 2 for #DIV/0!, 3 for #VALUE!, 4 for #REF!, 5 for #NAME!, 6
for #NUM!, and 7 for #N/A. Any other value in the referenced cell returns the error value
#N/A. For example, if cell A1 contains a formula that displays the error value #NAME!, the
formula =ERROR.TYPE(A1) returns 5 . If A1 contains the text Microsoft Excel, the formula
returns #N/A.
The COUNTBLANK function
The COUNTBLANK function counts the number of empty cells in the specified range,
which is its only argument. This function is tricky because formulas that evaluate to null
text strings, such as =" ", or to zero might seem empty, but they aren’t and therefore aren’t
counted.
Using the IS information functions
You can use the ISBLANK, ISERR, ISERROR, ISEVEN, ISFORMULA, ISLOGICAL, ISNA,
ISNONTEXT, ISNUMBER, ISODD, ISREF, and ISTEXT functions to determine whether a referenced
cell or range contains the corresponding type of value.
Search JabSto ::




Custom Search