Information Functions /text/scalc/01/04060104.xhp information functions Function Wizard; information functions; information functions 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
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. Syntax 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
"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. Example =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. Syntax CURRENT() Example =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. Syntax 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. Example 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 Syntax ISREF(Value) Value is the value to be tested, to determine whether it is a reference. Example =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. Syntax ISERR(Value) Value is any value or expression which is tested to see whether an error value other than #N/A is present. Example =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. Syntax ISERROR(Value) Value is or refers to the value to be tested. ISERROR() returns TRUE if there is an error and FALSE if not. Example =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.
ISFORMULA function recognizing formula cells formula cells;recognizing mw inserted "recognizing..." and "formula cells;" 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 if the cell C4 contains the number 5.
ISEVEN_ADD function ISEVEN_ADD Tests for even numbers. Returns 1 if the number divided by 2 returns a whole number. Syntax ISEVEN_ADD(Number) Number is the number to be tested. Example =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. 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 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. Syntax ISBLANK(Value) Value is the content to be tested. Example =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. Syntax ISLOGICAL(Value) Returns TRUE if Value is a logical value (TRUE or FALSE), and returns FALSE otherwise. Example =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. Syntax ISNA(Value) Value is the value or expression to be tested. Example =ISNA(D3) returns FALSE as a result.
ISTEXT function cell contents;text ISTEXT Returns TRUE if the cell contents refer to text. If an error occurs, the function returns FALSE. Syntax ISTEXT(Value) Value is a value, number, Boolean value, or an error value to be tested. Example =ISTEXT(D9) returns TRUE if cell D9 contains the text abcdef. =ISTEXT(C3) returns FALSE if cell C3 contains the number 3.
ISODD_ADD function ISODD_ADD Returns TRUE (1) if the number does not return a whole number when divided by 2. Syntax ISODD_ADD(Number) Number is the number to be tested. Example =ISODD_ADD(5) returns 1.
ISNUMBER function cell contents;numbers mw added "cell contents;" 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 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, FALSE or #NA. If an error occurs, other than #NA, the function returns the error value. Syntax 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 and errors as 0. Example =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. Syntax NA() Example =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. Syntax 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. Syntax 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(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, $[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.