LikeOffice.com

excel utility

Keep In Touch:
 contact us  facebook
 
Get HELP with your Excel Project:
 Excel Formula
 List of Excel Formula examples
 
Home >> excel 2007 >> Excel 2007 - Information functions

This page presents how to work with Microsoft Excel 2007 features and its redesigned interface. You may also visit our Excel Question page.

 

   Information functions

   CELL function

   ERROR.TYPE function

   INFO function

   ISEVEN function

   IS functions

   ISODD function

   N function

   NA function

   TYPE function


 

Information functions

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

Function

Description

CELL

Returns information about the formatting, location, or contents of a cell

ERROR.TYPE

Returns a number corresponding to an error type

INFO

Returns information about the current operating environment

ISBLANK

Returns TRUE if the value is blank

ISERR

Returns TRUE if the value is any error value except #N/A

ISERROR

Returns TRUE if the value is any error value

ISEVEN

Returns TRUE if the number is even

ISLOGICAL

Returns TRUE if the value is a logical value

ISNA

Returns TRUE if the value is the #N/A error value

ISNONTEXT

Returns TRUE if the value is not text

ISNUMBER

Returns TRUE if the value is a number

ISODD

Returns TRUE if the number is odd

ISREF

Returns TRUE if the value is a reference

ISTEXT

Returns TRUE if the value is text

N

Returns a value converted to a number

NA

Returns the error value #N/A

TYPE

Returns a number indicating the data type of a value

 

See Also

 

 

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.

info_type

Returns

"address"

Reference of the first cell in reference, as text.

"col"

Column number of the cell in reference.

"color"

The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero).

"contents"

Value of the upper-left cell in reference; not a formula.

"filename"

Filename (including full path) of the file that contains reference, as text. Returns empty text ("") if the worksheet that contains reference has not yet been saved.

"format"

Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns "-" at the end of the text value if the cell is formatted in color for negative values. Returns "()" at the end of the text value if the cell is formatted with parentheses for positive or all values.

"parentheses"

The value 1 if the cell is formatted with parentheses for positive or all values; otherwise returns 0.

"prefix"

Text value corresponding to the "label prefix" of the cell. Returns single quotation mark (') if the cell contains left-aligned text, double quotation mark (") if the cell contains right-aligned text, caret (^) if the cell contains centered text, backslash (\) if the cell contains fill-aligned text, and empty text ("") if the cell contains anything else.

"protect"

The value 0 if the cell is not locked; otherwise returns 1 if the cell is locked.

"row"

Row number of the cell in reference.

"type"

Text value corresponding to the type of data in the cell. Returns "b" for blank if the cell is empty, "l" for label if the cell contains a text constant, and "v" for value if the cell contains anything else.

"width"

Column width of the cell, rounded off to an integer. Each unit of column width is equal to the width of one character in the default font size.

  • 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.

If the Excel format is

The CELL function returns

General

"G"

0

"F0"

#,##0

",0"

0.00

"F2"

#,##0.00

",2"

$#,##0_);($#,##0)

"C0"

$#,##0_);[Red]($#,##0)

"C0-"

$#,##0.00_);($#,##0.00)

"C2"

$#,##0.00_);[Red]($#,##0.00)

"C2-"

0%

"P0"

0.00%

"P2"

0.00E+00

"S2"

# ?/? or # ??/??

"G"

m/d/yy or m/d/yy h:mm or mm/dd/yy

"D4"

d-mmm-yy or dd-mmm-yy

"D1"

d-mmm or dd-mmm

"D2"

mmm-yy

"D3"

mm/dd

"D5"

h:mm AM/PM

"D7"

h:mm:ss AM/PM

"D6"

h:mm

"D9"

h:mm:ss

"D8"

 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

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. 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.

  1. 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.

 

1

2

3

4

5

6

7

8

A

B

C

Data

 

 

5-Mar

 

 

TOTAL

 

 

Formula

Description

Result

=CELL("row", A20)

The row number of cell A20

20

=CELL("format", A2)

The format code of cell A2

D2 (d-mmm)

=CELL("contents", A3)

The content of cell A3

TOTAL

=CELL("type", A2)

The data type of cell A2

v (value)

 

See Also

 

 

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.

If error_val is

ERROR.TYPE returns

#NULL!

1

#DIV/0!

2

#VALUE!

3

#REF!

4

#NAME?

5

#NUM!

6

#N/A

7

#GETTING_DATA

8

Anything else

#N/A


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

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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.

 

1

2

3

A

 

Data

#NULL!

=1/0

Formula

Description (Result)

=ERROR.TYPE(A2)

Number of the #NULL! error (1)

=IF(ERROR.TYPE(A3)<3,CHOOSE(ERROR.TYPE(A3),"Ranges do not intersect","The divisor is zero"))

Checks cell A3 to see whether the cell contains either the #NULL! error value or the #DIV/0! error value. If it does, then the number for the error value is used in the CHOOSE worksheet function to display one of two messages; otherwise, the #N/A error value is returned. (The divisor is zero.)

 

See Also

 

 

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.

Type_text

Returns

"directory"

Path of the current directory or folder.

"numfile"

Number of active worksheets in the open workbooks.

"origin"

Returns the absolute cell reference of the top and leftmost cell visible in the window, based on the current scrolling position, as text prepended with "$A:". This value is intended for for Lotus 1-2-3 release 3.x compatibility. The actual value returned depends on the current reference style setting. Using D9 as an example, the return value would be:

  • A1 reference style   "$A:$D$9".
  • R1C1 reference style  "$A:R9C4"

"osversion"

Current operating system version, as text.

"recalc"

Current recalculation mode; returns "Automatic" or "Manual".

"release"

Version of Microsoft Excel, as text.

"system"

Name of the operating environment:
Macintosh = "mac"
Windows = "pcdos"

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

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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.

 

1

2

A

B

Formula

Description (Result)

=INFO("numfile")

Number of active worksheets (varies)

=INFO("recalc")

Recalculation mode for the workbook (Automatic or Manual)

 

See Also

 

 

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

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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.

 

1

2

3

4

A

B

Formula

Description (Result)

=ISEVEN(-1)

Checks whether -1 is even (FALSE)

=ISEVEN(2.5)

Checks whether 2.5 is even (TRUE)

=ISEVEN(5)

Checks whether 5 is even (FALSE)

 

See Also

 

 

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.

Function

Returns TRUE if

ISBLANK

Value refers to an empty cell.

ISERR

Value refers to any error value except #N/A.

ISERROR

Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).

ISLOGICAL

Value refers to a logical value.

ISNA

Value refers to the #N/A (value not available) error value.

ISNONTEXT

Value refers to any item that is not text. (Note that this function returns TRUE if the value refers to a blank cell.)

ISNUMBER

Value refers to a number.

ISREF

Value refers to a reference.

ISTEXT

Value refers to text.

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

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. 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.

  1. 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.

 

1

2


3

4

5


6

A

B

C

Formula

Description

Result

=ISLOGICAL(TRUE)

Checks whether TRUE is a logical value

TRUE

=ISLOGICAL("TRUE")

Checks whether "TRUE" is a logical value

FALSE

=ISNUMBER(4)

Checks whether 4 is a number

TRUE

=ISREF(G8)

Checks whether G8 is a valid reference

TRUE

=ISREF(XYZ1)

Checks whether XYZ1 is a valid reference

FALSE

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

  1. Press CTRL+C.
  2. In Excel, create a blank workbook or worksheet.
  3. 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.

  1. 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.

 

1

2

3

4

5

6

7

8


9


10


11


12


13


14

A

B

C

Data

 

 

Gold

 

 

Region1

 

 

#REF!

 

 

330.92

 

 

#N/A

 

 

Formula

Description

Result

=ISBLANK(A2)

Checks whether cell A2 is blank.

FALSE

=ISERROR(A4)

Checks whether the value in cell A4, #REF!, is an error.

TRUE

=ISNA(A4)

Checks whether the value in cell A4, #REF!, is the #N/A error.

FALSE

=ISNA(A6)

Checks whether the value in cell A6, #N/A, is the #N/A error.

TRUE

=ISERR(A6)

Checks whether the value in cell A6, #N/A, is an error.

FALSE

=ISNUMBER(A5)

Checks whether the value in cell A5, 330.92, is a number.

TRUE

=ISTEXT(A3)

Checks whether the value in cell A3, Region1, is text.

TRUE

 

See Also

 

 

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

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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.

 

1

2

3

4

A

B

Formula

Description (Result)

=ISODD(-1)

Checks whether -1 is odd (TRUE)

=ISODD(2.5)

Checks whether 2.5 is odd (FALSE)

=ISODD(5)

Checks whether 5 is odd (TRUE)

 

See Also

 

 

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.

If value is or refers to

N returns

A number

That number

A date, in one of the built-in date formats available in Microsoft Excel

The serial number of that date

TRUE

1

FALSE

0

An error value, such as #DIV/0!

The error value

Anything else

0


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

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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.

 

1

2

3

4

5

A

 

Data

7

Even

TRUE

4/17/2008

Formula

Description (Result)

=N(A2)

Because A2 contains a number, it is returned (7)

=N(A3)

Because A3 contains text, 0 is returned (0, see above)

=N(A4)

Because A4 is the logical value TRUE, 1 is returned (1, see above)

=N(A5)

Because A5 is a date, the serial number is returned (varies with the date system used)

=N("7")

Because "7" is text, 0 is returned (0, see above)

 

See Also

 

 

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

 

 

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

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and press CTRL+V.
  3. 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.

 

1

2

A

 

Data

Smith

Formula

Description (Result)

=TYPE(A2)

Checks the type of the value above (2)

=TYPE("Mr. "&A2)

Checks the type of "Mr. Smith" (2)

=TYPE(2+A2)

Checks the type of the formula, which returns the error #VALUE! (16)

=TYPE({1,2;3,4})

Checks the type of an array constant (64)

 

                  

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
©2003-2013 LikeOffice - Privacy Policy - Contact us