Information Functions

+ +

+ + INFO function + + + + +INFO +Returns specific information about the current working environment. The function receives a single text argument and returns data depending on that parameter. + +

+INFO("Type") +The following table lists the values for the text parameter Type and the return values of the INFO function. + + + + + Value for "Type"Do not translate the text values in this column below + + + Return value + + + + + "osversion" + + + Always "Windows (32-bit) NT 5.01", for compatibility reasons + + + + + "system" + + + The type of the operating system.
"WNT" for Microsoft Windows
"LINUX" for Linux
"MACOSX" for macOS
"SOLARIS" for Solaris + + + + + "release" + + + The product release identifier, for example "300m25(Build:9876)" + + + + + "numfile" + + + Always 1, for compatibility reasons + + + + + "recalc" + + + Current formula recalculation mode, either "Automatic" or "Manual" (localized into %PRODUCTNAME language) + + +

+ +Other spreadsheet applications may accept localized values for the Type parameter, but %PRODUCTNAME Calc will only accept the English values. + +

+=INFO("release") returns the product release number of the %PRODUCTNAME in use.Do not translate "release" +=INFO(D5) with cell D5 containing a text string system returns the operation system type.Do not translate the hlp_literal system +

+ +

+ + CURRENT function + + + + +CURRENT +This function returns the result to date of evaluating the formula of which it is a part (in other words the result as far as that evaluation has got). Its main use is together with the STYLE() function to apply selected styles to a cell depending on the cell contents. + +

+CURRENT() + +

+=1+2+CURRENT() +The example returns 6. The formula is calculated from left to right as: 1 + 2 equals 3, giving the result to date when CURRENT() is encountered; CURRENT() therefore yields 3, which is added to the original 3 to give 6. +=A2+B2+STYLE(IF(CURRENT()>10;”Red”;”Default”)) +The example returns A2 + B2 (STYLE returns 0 here). If this sum is greater than 10, the style Red is applied to the cell. See the STYLE function for more explanation. +="choo"&CURRENT() +The example returns choochoo. +

+ +

+ + FORMULA function + formula cells;displaying formulas in other cells + displaying;formulas at any position + +mw added "formula cells;" and "displaying;" + + +FORMULA +Displays the formula of a formula cell as a text string. + +

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

+If cell A8 contains the formula =SUM(1;2;3) then +=FORMULA(A8) returns the text =SUM(1;2;3). +

+ +

+ + ISREF function + references;testing cell contents + cell contents;testing for references + +mw added "references;" and "cell contents;" + + +ISREF +Tests if the argument is a reference. Returns TRUE if the argument is a reference, returns FALSE otherwise. When given a reference this function does not examine the value being referenced.i82565 +

+ +

+ISREF(Value) +Value is the value to be tested, to determine whether it is a reference. + +

+=ISREF(C5) returns the result TRUE because C5 is a valid reference. +=ISREF("abcdef") returns always FALSE because a text can never be a reference. +=ISREF(4) returns FALSE. +=ISREF(INDIRECT("A6")) returns TRUE, because INDIRECT is a function that returns a reference. +=ISREF(ADDRESS(1; 1; 2;"Sheet2")) returns FALSE, because ADDRESS is a function that returns a text, although it looks like a reference. +

+ +

+ + ISERR function + error codes;controlling + +mw added "error codes," + + +ISERR +Tests for error conditions, except the #N/A error value, and returns TRUE or FALSE. +

+ +

+ISERR(Value) +Value is any value or expression which is tested to see whether an error value other than #N/A is present. + +

+=ISERR(C8) where cell C8 contains =1/0 returns TRUE, because 1/0 is an error. +=ISERR(C9) where cell C9 contains =NA() returns FALSE, because ISERR() ignores the #N/A error. +

+ +

+ + ISERROR function + recognizing;general errors + +mw added "recognizing;" + + +ISERROR +Tests for error conditions, including the #N/A error value, and returns TRUE or FALSE. +

+ +

+ISERROR(Value) +Value is or refers to the value to be tested. ISERROR() returns TRUE if there is an error and FALSE if not. + +

+=ISERROR(C8) where cell C8 contains =1/0 returns TRUE, because 1/0 is an error. +=ISERROR(C9) where cell C9 contains =NA() returns TRUE. +

+ +

+ + IFERROR function + testing;general errors + + + + +IFERROR +Returns the value if the cell does not contains an error value, or the alternative value if it does. +

+IFERROR(Value; Alternate_value) +Value is the value or expression to be returned if it is not equal or results in an error. +Alternate_value is the value or expression to be returned if the expression or value of Value is equal or results in an error. + +

+=IFERROR(C8;C9) where cell C8 contains =1/0 returns the value of C9, because 1/0 is an error. +=IFERROR(C8;C9) where cell C8 contains 13 returns 13, the value of C8, which is not an error. +

+ +

+ + ISFORMULA function + recognizing formula cells + formula cells;recognizing + +mw inserted "recognizing..." and "formula cells;" + + +ISFORMULA +Returns TRUE if a cell is a formula cell. +

+ +

+ISFORMULA(Reference) +Reference indicates the reference to a cell in which a test will be performed to determine if it contains a formula. + +

+=ISFORMULA(C4) returns FALSE if the cell C4 contains the number 5. +

+ +

+ + ISEVEN function + even integers + + + + +ISEVEN +Returns TRUE if the value is an even integer, or FALSE if the value is odd. + +

+ISEVEN(Value) +Value is the value to be checked. +If Value is not an integer any digits after the decimal point are ignored. The sign of Value is also ignored. + +

+=ISEVEN(48) returns TRUE +=ISEVEN(33) returns FALSE +=ISEVEN(0) returns TRUE +=ISEVEN(-2.1) returns TRUE +=ISEVEN(3.999) returns FALSEsee also ISODD +

+ +

+ + ISEVEN_ADD function + + + + +ISEVEN_ADD +Tests for even numbers. Returns 1 if the number divided by 2 returns a whole number. +

+ +

+ISEVEN_ADD(Number) +Number is the number to be tested. + +

+=ISEVEN_ADD(5) returns 0. +=ISEVEN_ADD(A1) returns 1 if cell A1 contains the number 2. +

+ +

+ + ISNONTEXT function + cell contents;no text + +mw added "cell contents;" + + +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 TRUE. + +

+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 if cell D2 contains the text abcdef. +=ISNONTEXT(D9) returns TRUE if cell D9 contains the number 8. +

+ +

+ + ISBLANK function + blank cell contents + empty cells; recognizing + +mw added "blank..." and "empty cells;" + + +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. +

+ +

+ISBLANK(Value) +Value is the content to be tested. + +

+=ISBLANK(D2) returns FALSE as a result. +

+ +

+ + ISLOGICAL function + number formats;logical + logical number formats + +mw added "number formats;" and "logical..." + + +ISLOGICAL +Tests for a logical value (TRUE or FALSE). +If an error occurs, the function returns FALSE. + +

+ISLOGICAL(Value) +Returns TRUE if Value is a logical value (TRUE or FALSE), and returns FALSE otherwise. + +

+=ISLOGICAL(99) returns FALSE, because 99 is a number, not a logical value. +=ISLOGICAL(ISNA(D4)) returns TRUE whatever the contents of cell D4, because ISNA() returns a logical value. +

+ +

+ + ISNA function + #N/A error;recognizing + +mw added "#N/A ..." + + +ISNA +Returns TRUE if a cell contains the #N/A (value not available) error value. +If an error occurs, the function returns FALSE. + +

+ISNA(Value) +Value is the value or expression to be tested. + +

+=ISNA(D3) returns FALSE as a result. +

+ +

+ + IFNA function + #N/A error;testing + + + + +IFNA +Returns the value if the cell does not contains the #N/A (value not available) error value, or the alternative value if it does. +

+IFNA(Value; Alternate_value) +Value is the value or expression to be returned if it is not equal or results in an #N/A error. +Alternate_value is the value or expression to be returned if the expression or value of Value is equal or results in an #N/A error. + +

+=IFNA(D3;D4) returns the value of D3 if D3 does not result in an #N/A error, or D4 if it does. +

+ +

+ + ISTEXT function + cell contents;text + + + + +ISTEXT +Returns TRUE if the cell contents refer to text. +If an error occurs, the function returns FALSE. + +

+ISTEXT(Value) +Value is a value, number, Boolean value, or an error value to be tested. + +

+=ISTEXT(D9) returns TRUE if cell D9 contains the text abcdef. +=ISTEXT(C3) returns FALSE if cell C3 contains the number 3. +

+ +

+ + ISODD function + odd integers + + + + +ISODD +Returns TRUE if the value is odd, or FALSE if the number is even. + +

+ISODD(value) +Value is the value to be checked. +If Value is not an integer any digits after the decimal point are ignored. The sign of Value is also ignored. + +

+=ISODD(33) returns TRUE +=ISODD(48) returns FALSE +=ISODD(3.999) returns TRUE +=ISODD(-3.1) returns TRUEsee also ISEVEN +

+ +

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

+ +

+ISODD_ADD(Number) +Number is the number to be tested. + +

+=ISODD_ADD(5) returns 1. +

+ +

+ + ISNUMBER function + cell contents;numbers + +mw added "cell contents;" + + +ISNUMBER +Returns TRUE if the value refers to a number. +

+ +

+ISNUMBER(Value) +Value is any expression to be tested to determine whether it is a number or text. + +

+=ISNUMBER(C3) returns TRUE if the cell C3 contains the number 4. +=ISNUMBER(C2) returns FALSE if the cell C2 contains the text abcdef. +

+ +

+ + N function + + + + +N +Returns the numeric value of the given parameter. Returns 0 if parameter is text or FALSE. +If an error occurs the function returns the error value. + +

+N(Value) +Value is the parameter to be converted into a number. N() returns the numeric value if it can. It returns the logical values TRUE and FALSE as 1 and 0 respectively. It returns text as 0. + +

+=N(123) returns 123 +=N(TRUE()) returns 1 +=N(FALSE()) returns 0 +=N("abc") returns 0 +=N(1/0) returns #DIV/0! +

+ +

+ + NA function + #N/A error;assigning to a cell + +mw added "#N/A error;" + + +NA +Returns the error value #N/A. + +

+NA() + +

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

+ +

+ + TYPE function + + + + +TYPE +Returns the type of value, where 1 = number, 2 = text, 4 = Boolean value, 8 = formula, 16 = error value, 64 = array. +

+ +

+TYPE(Value) +Value is a specific value for which the data type is determined. + +Example (see example table above) +=TYPE(C2) returns 2 as a result. +=TYPE(D9) returns 1 as a result. +

+ +

+ + CELL function + cell information + information on cells + +mw added two entries + + +CELL +Returns information on address, formatting or contents of a cell. + +

+CELL("InfoType" [; Reference]) +InfoType is the character string that specifies the type of information. The character string is always in English. Upper or lower case is optional. + + + + + InfoType + + + 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.ods'#$Sheet1.D2) returns 'file:///X:/dr/test.ods'#$Sheet1.$D$2. + + + + + FILENAME + + + Returns the file name and the sheet number of the referenced cell. + =CELL("FILENAME";D2) returns 'file:///X:/dr/own.ods'#$Sheet1, if the formula in the current document X:\dr\own.ods is located in Sheet1. + =CELL("FILENAME";'X:\dr\test.ods'#$Sheet1.D2) returns 'file:///X:/dr/test.ods'#$Sheet1. + + + + + COORD + + + Returns the complete cell address in Lotus™ 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, $[officename] 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. +