IBM Lotus Symphony


Information Functions

This category contains the Information functions. The functions are: CELL, CURRENT, FORMULA, ISBLANK , ISERR , ISERROR , ISEVEN_ADD, ISFORMULA, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISODD_ADD, ISREF, ISTEXT, N, NA, INFO, TYPE .

The data in the following table serves as the basis for some of the examples in the function descriptions:

Table 1. Sample data
Row C D
2 x value y value
3 -5 -3
4 -2 0
5 -1 1
6 0 3
7 2 4
8 4 6
9 6 8

CURRENT

Calculates the current value of a formula at the actual position.

Syntax

CURRENT()

Example

For example, to assign the current value to the current cell:

Input number 1 in A1,and number 2 in B1

enter a formula:=A1+B1+CURRENT() in C1 and press Instant Pilot:Functions button and the value is set as:

1+2+CURRENT() yields 6 (1+2=CURRENT+CURRENT=6)

or you can try: 1+CURRENT()+2 yields 4 (1=CURRENT+CURRENT+2=4)

FORMULA

Displays the formula of a formula cell at any position. The formula will be returned as a string in the Reference position. If no formula cell can be found,or if the presented argument is not a reference, the error value #N/A is set.

Syntax

FORMULA()

Example

The cell A8 contains the result of a formula having the value 23. You can now use the Formula function in cell A1 to display the formula in cell A8.

=FORMULA(A8)

ISREF

Tests if the content of one or several cells is a reference. Verifies the type of references in a cell or a range of cells.

If an error occurs, the function returns a logical or numerical value.

Syntax

ISREF(value)

Value is the value to be tested, to determine whether it is a reference.

Example

ISREF(C5) returns the result TRUE

See also the following functions:

TYPE .

ISERR

Returns TRUE if the value refers to any error value except #N/A. You can use this function to control error values in certain cells.

If an error occurs, the function returns a logical or numerical value.

Syntax

ISERR(value)

Value is any value or expression in which a test is performed to determine whether an error value not equal to #N/A is present.

Example

ISERR(C5) returns FALSE.

See also the following functions:

TYPE.

ISERROR

The ISERROR tests if the cells contain general error values. ISERROR recognizes the #N/A error value.

If an error occurs, the function returns a logical or numerical value.

Syntax

ISERROR(value)

Value is any value where a test is performed to determine whether it is an error value.

Example

ISERROR(C8) returns FALSE.

See also the following functions:

TYPE.

ISFORMULA

Returns TRUE if a cell is a formula cell.

If an error occurs, the function returns a logical or numerical value.

Syntax

ISFORMULA(reference)

Reference indicates the reference to a cell in which a test will be performed to determine if it contains a reference.

Example

ISFORMULA(C4) returns FALSE as a result.

ISEVEN_ADD

Tests for even numbers. Returns TRUE (1) if the number returns a whole number when divided by 2.

Syntax

ISEVEN_ADD(Number)

Number: the number to be tested.

Example

=ISEVEN_ADD(5) returns 0.

ISNONTEXT

Tests if the cell contents are text or numbers, and returns FALSE if the contents are text.

If an error occurs, the function returns a logical or numerical value.

Syntax

ISNONTEXT(value)

Value is any value or expression where a test is performed to determine whether it is a text or numbers or a Boolean value.

Example

ISNONTEXT(D2) returns FALSE.

ISNONTEXT(D9) returns TRUE.

See also the following functions:

TYPE .

ISBLANK

Returns TRUE if the reference to a cell is blank. This function is used to determine if the content of a cell is empty. A cell with a formula inside is not empty.

If an error occurs, the function returns a logical or numerical value.

Syntax

ISBLANK(value)

Value is the content to be tested.

Example

ISBLANK(D2) returns FALSE as a result.

See also the following functions:

TYPE .

ISLOGICAL

Returns TRUE if the cell contains a logical number format. The function is used in order to check for both TRUE and FALSE values in certain cells.

If an error occurs, the function returns a logical or numerical value.

Syntax

ISLOGICAL(value)

Value is the value to be tested for logical number format.

Example

ISLOGICAL(D5) returns FALSE as a result.

See also the following functions:

TYPE.

ISNA

Returns TRUE if a cell contains the #N/A (value not available) error value.

If an error occurs, the function returns a logical or numerical value.

Syntax

ISNA(value)

Value is the value or expression to be tested.

Example

ISNA(D3) returns FALSE as a result.

See also the following functions:

TYPE

ISTEXT

Returns TRUE if the cell contents refer to text.

If an error occurs, the function returns a logical or numerical value.

Syntax

ISTEXT(value)

Value is a value, number, Boolean value, or an error value to be tested.

Example

ISTEXT(D9) returns the result TRUE.

ISTEXT(C3) returns FALSE as a result.

See also the following functions:

TYPE

ISODD_ADD

Returns TRUE (1) if the number does not return a whole number when divided by 2.

Syntax

ISODD_ADD(Number)

Number: the number to be tested.

Example

=ISODD_ADD(5) returns 1.

ISNUMBER

Returns TRUE if the value refers to a number.

If an error occurs, the function returns a logical or numerical value.

Syntax

ISNUMBER(value)

Value is any expression to be tested to determine whether it is a number or text.

Example

ISNUMBER(C3) returns the result TRUE.

ISNUMBER(C2) returns FALSE as a result.

See also the following functions:

TYPE

N

Returns the number 1, if the parameter is TRUE. Returns the parameter, if the parameter is a number. Returns the number 0 for other parameters.

If an error occurs, the function returns a logical or numerical value.

Syntax

N(value)

Value is the parameter to be converted into a number.

Example

N(TRUE) returns 1

N(FALSE) returns 0

N(100) returns 100

N("abc") returns 0

See also the following functions:

T .

NA

Returns the error value #N/A.

Syntax

NA()

Example

NA() converts the contents of the cell into #N/A.

See also the following functions:

ISREF , ISERR, ISERROR, ISFORMULA, ISNONTEXT, ISBLANK, ISLOGICAL, ISNA, ISTEXT, ISNUMBER.

TYPE

Returns the type of value.

If an error occurs, the function returns a logical or numerical value.

Syntax

TYPE(value)

Value is a specific value for which the data type is determined. Value 1 = number, value 2 = text, value 4 = Boolean value, value 8 = formula, value 16 = error value.

Examples

TYPE(C2) returns 2 as a result.

TYPE(D9) returns 1 as a result.

INFO

Returns information for the current 1-2-3 session.

Syntax

Attribute is one of the following items, entered as text.

  • Support attributes
  • Author
  • Creation-date
  • Editing-time
  • Worksheet-number
  • Setup-International-Currency-Default
  • Setup-Recalculate-Order

CELL

Returns information on address, formatting or contents of a cell.

Syntax

CELL(Info_type; Reference)

Info_type is the character string that specifies the type of information. The character string is always in English. Upper or lower case is optional.

Info_type Meaning
COL

Returns the number of the referenced column.

Cell("COL";D2) returns 4.

ROW

Returns the number of the referenced row.

Cell("ROW";D2) returns 2.

SHEET

Returns the number of the referenced sheet.

Cell("Sheet";Sheet3.D2) returns 3.

ADDRESS

Returns the absolute address of the referenced cell.

CELL("ADDRESS";D2) returns $D$2.

CELL("ADDRESS";Sheet3.D2) returns $Sheet3.$D$2.

CELL("ADDRESS";'X:\dr\test.sxc'#$Sheet1.D2) returns 'file:///X:/dr/test.sxc'#$Sheet1.$D$2.

FILENAME

Returns the file name and the sheet number of the referenced cell.

CELL("FILENAME";D2) returns 'file:///X:/dr/own.sxc'#$Sheet1, if the formula in the current document X:\dr\own.sxc is located in Sheet1.

CELL("FILENAME";'X:\dr\test.sxc'#$Sheet1.D2) returns 'file:///X:/dr/test.sxc'#$Sheet1.

COORD

Returns the complete cell address in Lotus(TM) notation.

CELL("COORD"; D2) returns $A:$D$2.

CELL("COORD"; Sheet3.D2) returns $C:$D$2.

CONTENTS Returns the contents of the referenced cell, without any formatting.
TYPE

Returns the type of cell contents.

b = blank. empty cell

l = label. Text, result of a formula as text

v = value. Value, result of a formula as a number

WIDTH Returns the width of the referenced column. The unit is the number of zeros (0) that fit into the column in the default text and the default size.
PREFIX

Returns the alignment of the referenced cell.

' = align left or left-justified

" = align right

^ = centered

\ = repeating (currently inactive)

PROTECT

Returns the status of the cell protection for the cell.

1 = cell is protected

0 = cell is not protected

FORMAT

Returns a character string that indicates the number format.

, = number with thousands separator

F = number without thousands separator

C = currency format

S = exponential representation, for example, 1.234+E56

P = percentage

In the above formats, the number of decimal places after the decimal separator is given as a number. Example: the number format #,##0.0 returns ,1 and the number format 00.000% returns P3

D1 = MMM-D-YY, MM-D-YY and similar formats

D2 = DD-MM

D3 = MM-YY

D4 = DD-MM-YYYY HH:MM:SS

D5 = MM-DD

D6 = HH:MM:SS AM/PM

D7 = HH:MM AM/PM

D8 = HH:MM:SS

D9 = HH:MM

G = All other formats

- (Minus) at the end = negative numbers are formatted in color

() (brackets) at the end = there is an opening bracket in the format code

COLOR Returns 1, if negative values have been formatted in color, otherwise 0.
PARENTHESES Returns 1 if the format code contains an opening bracket (, otherwise 0.

Reference (list of options) is the position of the cell to be examined. If Reference is a range, the cell moves to the top left of the range. If Reference is missing, IBM® Lotus® Symphony™ Spreadsheets uses the position of the cell in which this formula is located. Microsoft Excel uses the reference of the cell in which the cursor is positioned.


Product Feedback | Additional Documentation | Trademarks