Determining Cell Data Types with Excel 2007's TYPE Function

Excel 2007's TYPE function is an information function that returns the type of value in a cell. Aptly named, this 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 contains such a value. The TYPE function returns the following values, indicating the type of contents:

  • 1 for numbers

  • 2 for text

  • 4 for logical value (TRUE or FALSE)

  • 16 for an error value

  • 64 for an array range or constant

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 cell 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))

The following formula combines the NOT and TYPE functions and a mathematical operator nested inside an IF function. If the value in cell D11 contains either TRUE or FALSE, the formula returns the opposite value. Otherwise, the formula assumes that D11 contains a numeric value and adds the value in cell C11 to it.

=IF(TYPE(D11)=4,NOT(D11),D11+C11)

You can't use the TYPE function to determine whether a cell contains a formula, only the data type that the formula returns.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com