OpenOffice3.org Calc 3. Information Functions
CELL(info_type; Reference) Function
This function is very important. Returns information of interest related to the cell indicated. For example, lets you know the column number of a reference, the number of the row, which is the cell format, etc. The argument “Reference” is optional, contains the reference to the cell you want to evaluate. If you omit this argument, the function returns information about the cell you entered the function. The argument “info_type” contains a text data type. Indicates the type of information we get from the cell established. This argument can be uppercase or lowercase. Next I describe the different values that can take the argument “info_type”:
ADDRESS. Returns the reference of the cell indicated. The return value is an absolute reference. For example:
=CELL("ADDRESS") Returns the reference of the cell in which the function was entered, fof example, if returns $A$1, indicates that the function is entered in cell A1. In this case, omitted the argument “Reference”.
=CELL("address";H2) Returns $H$2. Returns the absolute reference of the indicated cell on the second argument.
COL. Returns an integer greater than zero. The return value is the number of the argument reference. For example:
=CELL("col";H6) Returns 8. The cell is in column 8.
=CELL("col";AKQ1) Returns 979. The cell AKQ1 is in column 979.
COLOR. Returns 1 or 0. If the cell of the second argument os configurated to show negative numbers in color, the function returns 1. Otherwise, the function returns 0.
You can set cell format so that when you enter a negative number is automatically shows, for example, red. This property is important because it allows show negative values in another color. For example:
=CELL("color";B32) If the function returns 1, meaning that when you enter a negative number in the cell B32 is automatically display in red or the color has been stablishet. If the function returns 0, are shown in the same color as the positive and negative numbers.
CONTENTS. Returns the contents of the cell indicated by the argument “Reference”. The function's return value is not formatted. For example:
=CELL("contents";B1) Returns the contents of cell B1, without numeric format. If cell B1 has the following value: $324.567,56, lthe function returns 324567,5555.
COORD. Returns a reference using the notation of LOTUS. For example:
=CELL("COORD";H3) Returns $A:$H$3. The cell H3 is located in the first sheet.
=CELL("coord";C2) Returns $A:$C$2. The cell C2 is located in the first sheet.
=CELL("coord";Sheet2.D1) Devuelve $B:$D$1. The cell D1 is located in the second sheet, Sheet2.
=CELL("coord";Sheet4.D8) Returns $D:$D$8. The cell D8 is located in the fourth sheet.
FILENAME. Returns the name of the sheet where the cell is located preceded by the full path. For example:
=CELL("filename";D9) in my example returns 'file:///C:/Exercise 1.ods'#$Sheet1. The file is on drive “C” in the file named “Exercise 1.ods”. The cell D9 is in the “Sheet1”.
FORMAT. Returns a string indicating the format of the cell. With this option you may know the numerical or date and time format that has evaluated the cell, as indicated below with several examples:
=CELL("Format";B1). If cell B1 contains the number 4.344,6. The function returns “,1” (the comma sign followed by the number 1). Indicates that the value is displayed with thousand separator and a decimal digit.
=CELL("Format";B2). If the cell B2 shows the following number: ($222,00). The function returns “C2-()”. The “C” indicates that the value is displayed in currency format with two decimal digits. The parentheses preceded by a minuns sign indicates that negative value are shown in parentheses. It is a notation, for example, used in accounting.
=CELL("Format";B3). Suppose that cell B3 is the following value: 3234. If the function returns “F0” indicates that the number is displayed without thousands separator and no decimal places.
=CELL("Format";B4). If cell B4 is the following value: 4,35E+004. The function returns “S2”. It means that the number is displayed in scientific notation with two decimal digits.
=CELL("Format";B5). If the cell B5 contains the following value: 2,33%. The function returns “P2”. The contents of the cell is shown as a percentage with two decimal digits.
Right away are the options for dates and times:
| Return value | Description of the format "Reference" |
|
“D1” |
Date format dd/mm/yyyy. Or date format dd/mm/yy. |
|
“D2” |
Date format dd/mmm |
|
“D3” |
Date format mm/aa |
|
“D4” |
Date and time format dd/mm/aaa hh:mm:ss |
|
“D5” |
Date format mm/dd |
|
“D6” |
Time format hh:mm:ss am/pm |
|
“D7” |
Time format formato hh:mm am/pm |
|
“D8” |
Time format formato hh:mm:ss |
|
“D9” |
Time format formato hh:mm |
The following table shows some examples to help understand this function to format date and time.
| Function | “Reference” | Return value | Description |
| =CELL("Format";B6) | 02/03/11 | "D1” | Corresponds to march 2, 2011. |
| =CELL("Format";B17) | 03/mar | "D2” | Corresponds to march 3. |
| =CELL("Format";B13) | 18/07/11 06:07 | "D3” | It's July 18th, 2011 at 6:07. |
| =CELL("Format";B15) | 18/07/2011 08:28:21 | "D4” | It's July 18th, 2011 at 8 am, 28 minutes and 21 seconds. |
| =CELL("Format";B15) | 04-08 | "D5” | Corresponds to 8 April. |
| =CELL("Format";B11) | 02:33:10 AM | "D6” | Time for 2 in the morning, 33 minutes and 10 seconds. |
| =CELL("Format";B12) | 06:22 PM | "D7” | It's 6 o'clock and 22 minutes. |
| =CELL("Format";B9) |
18:23:10
|
"D8” | This time corresponds to 6 pm, 23 minutes and 10 seconds. |
| =CELL("Format";B10) | 23:34 | "D9” | 11 pm with 34 minutes. |
If the argument “Reference” has a different format, the function returns “G”.
PARENTHESES. Returns 1 if the number format of the cell contains an open parenthesis “(”. Otherwise, it returns 0. It happens, for example, when set to negative numbers are presented within parentheses. Wneh entering a numeric value, the cell is automatically displayed in brackets. Consider the following example:
=CELL("parentheses";B2) Returns 1 if the cell format of the cell B2 includes parentheses, otherwise it returns zero.
PREFIX.
Returns a character that indicates the
horizontal alignment os the cell being examined. Next show different values
returned by the function for this option:
| Return value | Alignment |
|
' |
Align left |
|
" |
Right alignment |
| ^ | Centered |
| \ | Repeating an string |
The following example shows how to know the alignment that has a cell.
=CELDA("prefix";A1) Returns one of the characteres listed for the horizontal alingment of cell A1.
PROTECT. Returns 1 if the cell is protected. Otherwise, it returns 0. When set cell format, you can protect a cell to avoid modifiyng its content. We currently do not explain how to protect a cell. Consider the following example:
=CELL("protect";A1) If it returns 1, meaning that cell A1 is protected. If it returns zero, the cell A1 is unprotected.
ROW. Returns a positive integer. Indicates the row in which the cell is indicated by the argument “Reference”. For example:
=CELL("row";B13) Returns 13. Indicates that the cell is in row 13.
SHEET. Returns a positive integer. Returns the sheet number that is located the second argument. The sheets are numbered from the left to right starting with 1. The sheets are numbered from left to right starting with 1. For example:
=CELL("sheet";A9) If returns 3, meaning that the cell A9 is located in third sheet of the book.
TYPE. Returns a character that indicates the content type of the cell established by “Reference”. The value returned is described in the following table:
| Return value | Description |
| “b” | The cell is empty |
| “v” | The cell contains a number |
| “l” | The cell contanins text. |
For example:
=CELL("type";I25) The function returns “b” if the cell I25 is empty. Returns “v” if the cell contains a number. Returns “l” if the cell contains a text.
WIDTH. Returns a positive number. Returns the width of the column indicated by the second argument. For example:
=CELL("width";CA201) if it returns 15, indicating that the width of the column in which the cell is 15.