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("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, LibreOffice 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

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()

Näide tagastab vastuse 6. Valemit arvutatakse vasakult paremale niimoodi: 1 + 2 on 3, see on aktuaalne tulemus funktsioonini CURRENT() jõudmisel, CURRENT() on seega 3, mis liidetakse enne saadud tulemusele 3 ning vastuseks on 6.

=A2+B2+STYLE(IF(CURRENT()>10;”Punane”;”Vaikimisi”))

See näide tagastab tulemuse A2 + B2 (STYLE tagastab siin 0). Kui summa on suurem kui 10, omistatakse lahtrile stiil Punane. Rohkem teavet saab funktsiooni STYLE kirjeldusest.

="mää"&CURRENT()

Näide tagastab määmää.

FORMULA

Displays the formula of a formula cell as a text string.

Syntax

FORMULA(Reference)

Viide on viide valemit sisaldavale lahtrile.

Vigane viide või viide ilma valemita lahtrile annab tulemuseks veakoodi #N/A.

Example

If cell A8 contains the formula =SUM(1;2;3) then

=FORMULA(A8) returns the text =SUM(1;2;3).

IFERROR

Returns the value if the cell does not contains an error value, or the alternative value if it does.

Syntax

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.

Example

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

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.

Syntax

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.

Example

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

INFO

Tagastab aktiivset töökeskkonda iseloomustava teabe. Funktsioon vajab üht tekstilist lähteparameetrit ja tagastab sellele parameetrile vastavad andmed.

Süntaks

INFO("tüüp")

Järgnev tabel loetleb tekstiparameetri tüüp võimalikud väärtused ja neile vastavad funktsiooni INFO tagastusväärtused.

"Tüübi" väärtus	Tagastusväärtus
"osversion"	Ühilduvuspõhjustel alati "Windows (32-bit) NT 5.01"
"system"	Operatsioonisüsteemi tüüp. "WNT" Microsoft Windowsi puhul "LINUX" Linuxi puhul "SOLARIS" Solarise puhul
"release"	Toote väljalaske identifikaator, näiteks "300m25(Build:9876)"
"numfile"	Alati 1, ühilduvuspõhjustel
"recalc"	Aktiivne valemite taasarvutamise režiim, kas "Automaatne" või "Käsitsi" (tõlgitud LibreOffice'i kasutuskeelde)

Näide

=INFO("release") tagastab kasutatava LibreOffice'i väljalaskenumbri.

=INFO(D5) tagastab operatsioonisüsteemi tüübi, kui lahter D5 sisaldab tekstistringi system.

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

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) tagastab VÄÄR, kui C9 sisaldab =NA(), sest ISERR() eirab #N/A tüüpi viga.

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) tagastab TÕENE, kui C9 sisaldab =NA().

ISEVEN

Tagastab TÕENE, kui väärtus on paarisarv, ja VÄÄR, kui väärtus on paaritu arv.

Süntaks

ISEVEN(väärtus)

Väärtus on väärtus, mida kontrollitakse.

Kui väärtus pole täisarv, eiratakse kõiki pärast koma olevaid kohti. Samuti ei arvestata väärtuse märki.

Näide

=ISEVEN(48) tagastab TÕENE.

=ISEVEN(33) tagastab VÄÄR.

=ISEVEN(0) tagastab TÕENE.

=ISEVEN(-2,1) tagastab TÕENE.

=ISEVEN(3,999) tagastab VÄÄR.

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) tagastab 1, kui lahter A1 sisaldab arvu 2.

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.

ISLOGICAL

Tests for a logical value (TRUE or FALSE).

Vea korral tagastab funktsioon vastuse VÄÄR.

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)) tagastab TÕENE sõltumata lahtri D4 sisust, sest ISNA() tagastab tõeväärtuse.

ISNA

Returns TRUE if a cell contains the #N/A (value not available) error value.

Vea korral tagastab funktsioon vastuse VÄÄR.

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.

Vea korral tagastab funktsioon vastuse TÕENE.

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.

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.

ISODD

Tagastab TÕENE, kui väärtus on paaritu arv, ja VÄÄR, kui väärtus on paarisarv.

Süntaks

ISODD(väärtus)

Väärtus on väärtus, mida kontrollitakse.

Kui väärtus pole täisarv, eiratakse kõiki pärast koma olevaid kohti. Samuti ei arvestata väärtuse märki.

Näide

=ISODD(33) tagastab TÕENE.

=ISODD(48) tagastab VÄÄR.

=ISODD(3,999) tagastab TÕENE.

=ISODD(-3,1) tagastab TÕENE.

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.

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.

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") tagastab alati VÄÄR, sest tekst ei saa kunagi olla viide.

ISREF(4) tagastab VÄÄR.

=ISREF(INDIRECT("A6")) tagastab TÕENE, sest INDIRECT on funktsioon, mis tagastab viite.

=ISREF(ADDRESS(1; 1; 2; "Leht2")) tagastab VÄÄR, sest ADDRESS on funktsioon, mis tagastab teksti, ehkki see näeb välja nagu viide.

ISTEXT

Returns TRUE if the cell contents refer to text.

Vea korral tagastab funktsioon vastuse VÄÄR.

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.

N

Returns the numeric value of the given parameter. Returns 0 if parameter is text or FALSE.

Vea ilmnemisel tagastab funktsioon veateate.

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 as 0.

Example

=N(123) returns 123

=N(TRUE()) returns 1

=N(FALSE()) returns 0

=N("abc") returns 0

=N(1/0) tagastab #DIV/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, 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.