How to Install the Excel 2007 Analysis ToolPak
Using Excel 2007's IS Information Functions
Powers and Roots in Excel 2007: The POWER and SQRT Functions

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
An Overview of Excel 2007's Reference Functions
Using Boolean Expressions in Data Analysis with Excel 2007
Counting Cells with Excel 2007's COUNT Functions
Deconstructing Dates in Excel 2007 with DAY, WEEKDAY, MONTH, and YEAR
Using Logical Excel Functions in Excel 2007 Formulas
Advertisement

Inside Dummies.com