Information Functions
This category contains the Information functions.
The data in the following table serves as the basis for some of the examples in the function descriptions:
|
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 |
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, OpenOffice.org Calc 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.
CURRENT
Calculates the current value of a formula at the actual position.
Syntax
CURRENT()
Example
You can use this function in connection with the cell formatting function TEMPLATE. For example, to assign the current value in a new format to the current cell:
=CURRENT()+TEMPLATE("New")
1+2+CURRENT() yields 6 (1+2=CURRENT+CURRENT=6)
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(reference)
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)
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.
Syntax
ISBLANK(value)
Value is the content to be tested.
Example
ISBLANK(D2) returns FALSE as a result.
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.
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.
ISERROR
The ISERROR tests if the cells contain general error values. ISERROR recognizes the #N/A error 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.
ISEVEN_ADD
Tests for even numbers. Returns TRUE (1) if the number returns a whole number when divided by 2.
 | The functions whose names end with _ADD return the same results as the corresponding Microsoft Excel functions. Use the functions without _ADD to get results based on international standards. For example, the WEEKNUM function calculates the week number of a given date based on international standard ISO 6801, while WEEKNUM_ADD returns the same week number as Microsoft Excel. |
Syntax
ISEVEN_ADD(Number)
Number: the number to be tested.
Example
=ISEVEN_ADD(5) returns 0.
ISFORMULA
Returns TRUE if a cell is a formula cell.
Syntax
ISFORMULA(reference)
Reference indicates the reference to a cell in which a test will be performed to determine if it contains a formula.
Example
ISFORMULA(C4) returns FALSE as a result.
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.
Syntax
ISLOGICAL(value)
Value is the value to be tested for logical number format.
Example
ISLOGICAL(D5) returns FALSE as a result.
ISNA
Returns TRUE if a cell contains the #N/A (value not available) error value.
Syntax
ISNA(value)
Value is the value or expression to be tested.
Example
ISNA(D3) returns FALSE as a result.
ISNONTEXT
Tests if the cell contents are text or numbers, and returns FALSE if the contents are text.
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.
ISNUMBER
Returns TRUE if the value refers to a number.
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.
ISODD_ADD
Returns TRUE (1) if the number does not return a whole number when divided by 2.
| The functions whose names end with _ADD return the same results as the corresponding Microsoft Excel functions. Use the functions without _ADD to get results based on international standards. For example, the WEEKNUM function calculates the week number of a given date based on international standard ISO 6801, while WEEKNUM_ADD returns the same week number as Microsoft Excel. |
Syntax
ISODD_ADD(Number)
Number: the number to be tested.
Example
=ISODD_ADD(5) returns 1.
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.
Syntax
ISREF(value)
Value is the value to be tested, to determine whether it is a reference.
Example
ISREF(C5) returns the result TRUE
ISTEXT
Returns TRUE if the cell contents refer to text.
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.
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.
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
NA
Returns the error value #N/A.
Syntax
NA()
Example
NA() converts the contents of the cell into #N/A.
TYPE
Returns the type of 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.
Example (see example table above)
TYPE(C2) returns 2 as a result.
TYPE(D9) returns 1 as a result.
Index
information functions
Function Wizard, information
functions, information
CURRENT function
FORMULA function
formula cells,displaying formulas in other cells
displaying,formulas at any position
ISREF function
references,testing cell contents
cell contents,testing for references
ISERR function
error codes,controlling
ISERROR function
recognizing,general errors
ISFORMULA function
recognizing formula cells
formula cells,recognizing
ISEVEN_ADD function
ISNONTEXT function
cell contents,no text
ISBLANK function
blank cell contents
empty cells, recognizing
ISLOGICAL function
number formats,logical
logical number formats
ISNA function
#N/A error,recognizing
ISTEXT function
cell contents,text
ISODD_ADD function
ISNUMBER function
cell contents,numbers
N function
NA function
#N/A error,assigning to a cell
TYPE function
CELL function
cell information
information on cells
This help text is published from the OpenOffice-Help files under the Public Documentation License 1.0.
