Page 441 - Excel 2013 All-in-One For Dummies

Are you my type?
Excel provides another information function that returns the type of value in a cell. Aptly named, the TYPE function enables you to build formulas with the IF function that execute one type of behavior when the cell being tested contains a value and another when it contains text. The syntax of the TYPE function is
TYPE(value)
The value argument of the TYPE function can be any Excel entry: text, number, logical value, or even an Error value or a cell reference that con- tains such a value. The TYPE function returns the following values, indicat- ing the type of contents:
✦ 1 for numbers
✦ 2 for text
✦ 4 for logical value (TRUE or FALSE)
✦ 16 for Error value
✦ 64 for an array range or constant (see Book III, Chapter 1)
The following formula combines the CELL and TYPE functions nested within an IF function. This formula returns the type of the number formatting used in cell D11 only when the cell contains a value. Otherwise, it assumes that D11 contains a text entry, and it evaluates the type of alignment assigned to the text in that cell:
=IF(TYPE(D11)=1,CELL(“format”,D11),CELL(“prefix”,D11))
Using the IS functions
The IS information functions (as in ISBLANK, ISERR, and so on) are a large group of functions that perform essentially the same task. They evaluate
a value or cell reference and return the logical TRUE or FALSE, depending on whether the value is or isn’t the type for which the IS function tests. For example, if you use the ISBLANK function to test the contents of cell A1 as in
=ISBLANK(A1)
Excel returns TRUE to the cell containing the formula when A1 is empty and FALSE when it’s occupied by any type of entry.
Information, Please . . . 423
Book III Chapter 6
Lookup, Information, and Text Formulas