Information function in calc

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.
Info TypeMeaning
COLReturns the number of the referenced column.
=CELL("COL";D)Returns 4.
ROWReturns the number of the referenced row.
=CELL("ROW";D)Returns 2.
SHEETReturns the number of the referenced sheet.
=CELL("sheet";sheet3.D3)Returns 3.
ADDRESSReturns 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.
FILENAMEReturns the file name and the sheet number of the referenced cell.
=CELL("FILENAME";D2) returns 'file:///X:/dr/own.ods is located in Sheet1.
CELL('FILENAME";'X:\dr\test.ods'#$Sheet1.D2)returns 'file:///X:/dr/test.ods'#$Sheet1.
COORDReturns the complete cell address in LotusTMnotation.
=CELL("COORD"; D2) returns $A:$D$2.
=CELL("COORD"; Sheet3.D2) returns $C:$D$2.
CONTENTSReturns the contents of the referenced cell, without any formatting.
TYPEReturns the type of cell contents.
b=blank, empty cell
i=label, Text, result of a formula as text>
v=value, Value, result of a formula as a number
WIDTHReturns the width of the referenced column. The unit is the number of zeros (0) that fit the column in the default text and the default size.
PREFITReturns 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
FORMATReturns a character string that indicates the number format.
, = number with thousands separator
F = number without thousands separator
C = currency format
S = exponential reprsentation, for example, 1.234+E56
P = percentage
In the above formats, the number of decimal places after the decimal separator is given as a nimber. Example: the number format #,##0.0 returns,1 and the number format 00.000% returnsP3
D1 = MMM-D-YY, MM-D-YY, and similar formats
D2 = DD-MM
D3 = MM-YY
D4 = DD-MM-YY 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 un color
()(brackets) at the end = there is an opening brackets in the format code
COLORReturns 1, if negative Values have been formatted in color, otherwise.
PARENTHESESReturns 1, if the format code contains an opening bracket(, otherwise.

CURRENT

  • This function returns the result to date of Dipikaluating the formula of which it is a part (in other words the result as far as that Dipikaluation 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.

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

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

  • 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"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 indetifier, for example
"300m25(Build:9876)"
"numfile"Always 1, compatibility reasons
"recalc"Current formula recalculation mode, either "Automatic" or "Manual(localized into LibreOffice language)
Example

=INFO(“release”) returns the product release number of the LibreOffice in use.
=INFO(D5) with cell D5 containing a text string system returns the operation system type

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.
  • If an error occurs, the function returns a logical or numerical value.
    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.
  • If an error occurs, the function returns a logical or numerical value.
    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

  • Tests for error conditions, including the #N/A error value, and returns TRUE or FALSE.
  • If an error occurs, the function returns a logical or numerical value.
    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.

ISEVEN

  • Returns TRUE if the value is an even integer, or FALSE if the value is odd.
    Syntax
    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.
Example

=ISEVEN(48) returns TRUE
=ISEVEN(33) returns FALSE
=ISEVEN(0) returns TRUE
=ISEVEN(-2.1) returns TRUE
=ISEVEN(3.999) returns FALSE

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.

ISFORMULA

  • Returns TRUE if a cell is a formula cell.
  • If an error occurs, the function returns a logical or numerical value.
    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).
  • 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

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

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.

ISNUMBER

  • Returns TRUE if the value refers to a number.
  • If an error occurs, the function returns a logical or numerical value.
    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

  • Returns TRUE if the value is odd, or FALSE if the number is even.
    Syntax
    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.
Example

=ISODD(33) returns TRUE
=ISODD(48) returns FALSE
=ISODD(3.999) returns TRUE
=ISODD(-3.1) returns TRUE

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.

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.

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.
    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) returns #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.
  • If an error occurs, the function returns a logical or numerical value.
    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.

LibreOffice Clac Text Functions
Text Functions This section contains descriptions of the Text functions ...
Read More
LibreOffice Calc Array Functions
Array Functions This category contains the array functions.What is an ...
Read More
LibreOffice Calc Statistical Functions
Statistical Functions COUNT Counts how many numbers are in the ...
Read More
LibreOffice Calc Mathematical Functions
Mathematical Functions Mathematical This category contains the Mathematical functions for ...
Read More
LibreOffice Calc Logical Functions
LibreOffice Calc Logical Functions This category contains the Logical functions ...
Read More
LibreOffice Calc Date & Time Functions
Date & Time Functions TODAY Returns the current computer system ...
Read More
LibreOffice Calc Database Function
Database Function Parameters A B C D E 1 Name ...
Read More
LibreOffice Calc Information function
Information function in calc CELL Returns information on address, formatting ...
Read More

Contribute Now

SHOW YOUR SUPPORT FOR NIELITBHU..!!
Dear reader, thank you for joining us. Readers like you are an inspiration for us to pursue NIELITBHU. We need your support or cooperation to further empower NIELITBHU and reach NIELITBHU more people in every corner of the country. Your every support is invaluable to our future.

आप हमें सहयोग जरुर करें (Contribute Now)

Don’t miss Join Our Community!

Email Id