Information functions

Click one of the links in the following list to see detailed help about the function.

See Also

• List of worksheet functions (alphabetical)
• List of worksheet functions (by category)

Excel > Function reference > Information

CELL function

Excel 2007

This article describes the formula syntax and usage of the CELL function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel. Find links to more information about formatting the data in cells and applying cell styles in the See Also section.

Description

The CELL function returns information about the formatting, location, or contents of a cell. For example, if you want to verify that a cell contains a numeric value instead of text before you perform a calculation on it, you can use the following formula:

=IF(CELL("type", A1) = "v", A1 * 2, 0)

This formula calculates A1*2 only if cell A1 contains a numeric value, and returns 0 if A1 contains text or is blank.

Syntax

CELL(info_type, [reference])

The CELL function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• info_type  Required. A text value that specifies what type of cell information you want to return. The following list shows the possible values of the info_type argument and the corresponding results.

• reference  Optional. The cell that you want information about. If omitted, the information specified in the info_type argument is returned for the last cell that was changed. If the reference argument is a range of cells, the CELL function returns the information for only the upper left cell of the range.

CELL format codes

The following list describes the text values that the CELL function returns when the info_type argument is "format" and the reference argument is a cell that is formatted with a built-in number format.

 Note    If the info_type argument in the CELL function is "format" and you later apply a different format to the referenced cell, you must recalculate the worksheet to update the results of the CELL function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Add, change, or clear conditional formats
• Apply, create, or remove a cell style
• Information functions
• IS functions

Excel > Function reference > Information

ERROR.TYPE function

Excel 2007

Returns a number corresponding to one of the error values in Microsoft Excel or returns the #N/A error if no error exists. You can use ERROR.TYPE in an IF function to test for an error value and return a text string, such as a message, instead of the error value.

Syntax

ERROR.TYPE(error_val)

Error_val   is the error value whose identifying number you want to find. Although error_val can be the actual error value, it will usually be a reference to a cell containing a formula that you want to test.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Information functions
• IS functions

Excel > Function reference > Information

INFO function

Excel 2007

Security  Use this feature with caution. Sensitive or confidential information could be revealed to other users.

Returns information about the current operating environment.

Syntax

INFO(type_text)

Type_text   is text that specifies what type of information you want returned.

Important  In previous versions of Microsoft Office Excel, the "memavail", "memused", and "totmem" type_text values, returned memory information. These type_text values are no longer supported and now return a #N/A error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• CELL function
• Information functions

Excel > Function reference > Information

ISEVEN function

Excel 2007

Returns TRUE if number is even, or FALSE if number is odd.

Syntax

ISEVEN(number)

Number   is the value to test. If number is not an integer, it is truncated.

Remark

If number is nonnumeric, ISEVEN returns the #VALUE! error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• EVEN function
• Information functions
• ISODD function
• ODD function

Excel > Function reference > Information

IS functions

Excel 2007

This article describes the formula syntax and usage of the IS functions (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.

Description

Each of these functions, referred to collectively as the IS functions, checks the specified value and returns TRUE or FALSE depending on the outcome. For example, the ISBLANK function returns the logical value TRUE if the value argument is a reference to an empty cell; otherwise it returns FALSE.

You can use an IS function to get information about a value before performing a calculation or other action with it. For example, you can use the ISERROR function in conjunction with the IF function to perform a different action if an error occurs:

=IF(ISERROR(A1), "An error occurred.", A1 * 2)

This formula checks to see if an error condition exists in A1. If so, the IF function returns the message "An error occurred." If no error exists, the IF function performs the calculation A1*2.

Syntax

ISBLANK(value)

ISERR(value)

ISERROR(value)

ISLOGICAL(value)

ISNA(value)

ISNONTEXT(value)

ISNUMBER(value)

ISREF(value)

ISTEXT(value)

The IS function syntax has the following argument (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

• value  Required. The value that you want tested. The value argument can be a blank (empty cell), error, logical value, text, number, or reference value, or a name referring to any of these.

Remarks

• The value arguments of the IS functions are not converted. Any numeric values that are enclosed in double quotation marks are treated as text. For example, in most other functions where a number is required, the text value "19" is converted to the number 19. However, in the formula ISNUMBER("19"), "19" is not converted from a text value to a number value, and the ISNUMBER function returns FALSE.
• The IS functions are useful in formulas for testing the outcome of a calculation. When combined with the IF function, these functions provide a method for locating errors in formulas (see the following examples).

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Change the case of text
• Check if a cell contains text (case-insensitive)
• ERROR.TYPE function
• Hide error values and error indicators in cells
• IFERROR function
• Information functions
• ISEVEN function
• ISODD function
• TYPE function

Excel > Function reference > Information

ISODD function

Excel 2007

Returns TRUE if number is odd, or FALSE if number is even.

Syntax

ISODD(number)

Number   is the value to test. If number is not an integer, it is truncated.

Remark

If number is nonnumeric, ISODD returns the #VALUE! error value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• EVEN function
• Information functions
• ISEVEN function
• ODD function

Excel > Function reference > Information

N function

Excel 2007

Returns a value converted to a number.

Syntax

N(value)

Value   is the value you want converted. N converts values listed in the following table.

Remarks

• It is not generally necessary to use the N function in a formula, because Excel automatically converts values as necessary. This function is provided for compatibility with other spreadsheet programs.
• Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel for the Macintosh uses a different date system as its default.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

See Also

• Information functions
• T function

Excel > Function reference > Information

NA function

Returns the error value #N/A. #N/A is the error value that means "no value is available." Use NA to mark empty cells. By entering #N/A in cells where you are missing information, you can avoid the problem of unintentionally including empty cells in your calculations. (When a formula refers to a cell containing #N/A, the formula returns the #N/A error value.)

Syntax

NA( )

Remarks

• You must include the empty parentheses with the function name. Otherwise, Microsoft Excel will not recognize it as a function.
• You can also type the value #N/A directly into a cell. The NA function is provided for compatibility with other spreadsheet programs.

See Also

• Information functions
• IS functions

Excel > Function reference > Information

TYPE function

Excel 2007

Returns the type of value. Use TYPE when the behavior of another function depends on the type of value in a particular cell.

Syntax

TYPE(value)

Value   can be any Microsoft Excel value, such as a number, text, logical value, and so on.

Remarks

• TYPE is most useful when you are using functions that can accept different types of data, such as ARGUMENT and INPUT. Use TYPE to find out what type of data is returned by a function or formula.
• You cannot use TYPE to determine whether a cell contains a formula. TYPE only determines the type of the resulting, or displayed, value. If value is a cell reference to a cell that contains a formula, TYPE returns the type of the formula's resulting value.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.

 Note    Do not select the row or column headers.

Selecting an example from Help

3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.