Using Selected Information Functions

USING SELECTED INFORMATION FUNCTIONS

Information functions allow you to gather information about the contents of cells, their formatting, and the computing environment as well as 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 won’t be counted.