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 |
Calculates the current value of a formula at the actual position.
CURRENT()
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)
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(reference)
reference is a reference to a cell containing a formula.
An invalid reference or a reference to a cell with no formula results in the error value #N/A.
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.
ISREF(C5) returns the result TRUE
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.
ISERR(C5) returns FALSE.
ISERR(C9) where cell C9 contains =NA() returns FALSE, because ISERR() ignores the #N/A error.
The ISERROR tests if the cells contain general error values. ISERROR recognises 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.
ISERROR(C8) returns FALSE.
ISERROR(C9) where cell C9 contains =NA() returns TRUE.
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.
This function is only available if Analysis AddIn is installed.
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. |
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.
Returns TRUE if the reference to a cell is blank. This function is used to determine if the 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.
ISBLANK(D2) returns FALSE as a result.
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.
ISLOGICAL(D5) returns FALSE as a result.
ISLOGICAL(ISNA(D4)) returns TRUE whatever the contents of cell D4, because ISNA() returns a logical value.
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.
ISNA(D3) returns FALSE as a result.
Returns TRUE if the cell content is 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.
This function is only available if Analysis AddIn is installed.
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. |
ISODD_ADD(Number)
Number: the number to be tested.
=ISODD_ADD(5) returns 1.
Returns TRUE if the value is 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.
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(TRUE) returns 1
N(FALSE) returns 0
N("abc") returns 0
Returns the error value #N/A.
NA()
NA() converts the contents of the cell into #N/A.
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 8 = formula, value 16 = error.
TYPE(C2) returns 2 as a result.
TYPE(D9) returns 1 as a result.
Returns information on address, formatting or contents of a cell.
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 content of the referenced cell, without any formatting. |
TYPE |
Returns the type of cell content. 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 with the default size. |
PREFIX |
Returns the alignment of the referenced cell. ' = align left or left-justified " = align right ^ = centred \ = 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 colour () (brackets) at the end = there is an opening bracket in the format code |
COLOR |
Returns 1, if negative values have been formatted in colour, 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.