IBM Lotus Symphony
|
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:
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()
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)
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.
FORMULA()
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)
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.
ISREF(value)
Value is the value to be tested, to determine whether it is a reference.
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.
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.
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.
ISERROR(value)
Value is any value where a test is performed to determine whether it is an error value.
Returns TRUE if a cell is a formula cell.
If an error occurs, the function returns a logical or numerical value.
ISFORMULA(reference)
Reference indicates the reference to a cell in which a test will be performed to determine if it contains a reference.
ISFORMULA(C4) returns FALSE as a result.
Tests for even numbers. Returns TRUE (1) if the number returns a whole number when divided by 2.
ISEVEN_ADD(Number)
Number: the number to be tested.
=ISEVEN_ADD(5) returns 0.
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.
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.
ISNONTEXT(D2) returns FALSE.
ISNONTEXT(D9) returns TRUE.
See also the following functions:
TYPE .
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.
ISBLANK(value)
Value is the content to be tested.
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.
ISLOGICAL(value)
Value is the value to be tested for logical number format.
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.
ISNA(value)
Value is the value or expression to be tested.
Returns TRUE if the cell contents refer to text.
If an error occurs, the function returns a logical or numerical value.
ISTEXT(value)
Value is a value, number, Boolean value, or an error value to be tested.
ISTEXT(D9) returns the result TRUE.
ISTEXT(C3) returns FALSE as a result.
See also the following functions:
ISODD_ADD(Number)
Number: the number to be tested.
=ISODD_ADD(5) returns 1.
Returns TRUE if the value refers to a number.
If an error occurs, the function returns a logical or numerical value.
ISNUMBER(value)
Value is any expression to be tested to determine whether it is a number or text.
ISNUMBER(C3) returns the result TRUE.
ISNUMBER(C2) returns FALSE as a result.
See also the following functions:
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.
N(value)
Value is the parameter to be converted into a number.
N(TRUE) returns 1
N(FALSE) returns 0
N(100) returns 100
N("abc") returns 0
See also the following functions:
T .
NA()
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.
Returns the type of value.
If an error occurs, the function returns a logical or numerical value.
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.
TYPE(C2) returns 2 as a result.
TYPE(D9) returns 1 as a result.
Attribute is one of the following items, entered as text.
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.