Difference between revisions of "Documentation/How Tos/Calc: Spreadsheet functions"

From Apache OpenOffice Wiki
Jump to: navigation, search
(Initial content)
 
(sub-categorised)
Line 1: Line 1:
 
==List of Calc 'Spreadsheet' functions==
 
==List of Calc 'Spreadsheet' functions==
 
The so-called 'Spreadsheet' functions find values in tables, or cell references; they include '''LOOKUP''', '''SEARCH''', '''ADDRESS'''. They could thus also be described as 'Lookup' functions.
 
The so-called 'Spreadsheet' functions find values in tables, or cell references; they include '''LOOKUP''', '''SEARCH''', '''ADDRESS'''. They could thus also be described as 'Lookup' functions.
 +
  
 
{| border="0" cellpadding="0" cellspacing="10" align="left"
 
{| border="0" cellpadding="0" cellspacing="10" align="left"
 +
 +
|-valign="top"
 +
|colspan="2"|'''<big> Spreadsheet Lookup functions</big>'''
 +
 
|-valign="top"
 
|-valign="top"
 
|[[Documentation/How_Tos/Calc: ADDRESS function|'''ADDRESS''']]
 
|[[Documentation/How_Tos/Calc: ADDRESS function|'''ADDRESS''']]
Line 8: Line 13:
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: AREAS function|'''AREAS''']]
+
|[[Documentation/How_Tos/Calc: CHOOSE function|'''CHOOSE''']]
−
|returns the number of individual ranges in a multiple range.
+
|returns a value from a list, given an index number.
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: DDE function|'''DDE''']]
+
|[[Documentation/How_Tos/Calc: HLOOKUP function|'''HLOOKUP''']]
−
|returns information from other documents and applications, using the "DDE" protocol.
+
|returns a value from a table row, in the column found by lookup in the first row.
−
 
+
−
|-valign="top"
+
−
|[[Documentation/How_Tos/Calc: ERRORTYPE function|'''ERRORTYPE''']]
+
−
|returns the number corresponding to an error value.
+
  
 
|-valign="top"
 
|-valign="top"
Line 28: Line 29:
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: INFO function|'''INFO''']]
+
|[[Documentation/How_Tos/Calc: LOOKUP function|'''LOOKUP''']]
−
|returns information about the current working environment.  
+
|returns result from one single-cell-wide table, corresponding to a lookup search in another.
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: COLUMN function|'''COLUMN''']]
+
|[[Documentation/How_Tos/Calc: MATCH function|'''MATCH''']]
−
|returns the column number, given a reference.
+
|returns the position in a single row or column table matching a search criterion.
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: COLUMNS function|'''COLUMNS''']]
+
|[[Documentation/How_Tos/Calc: OFFSET function|'''OFFSET''']]
−
|returns the number of columns in a given reference.
+
|returns the contents of a cell, given a reference and a desired offset from that reference.
  
 
|-valign="top"
 
|-valign="top"
Line 43: Line 44:
 
|returns a value from a table column, in the row found by lookup in the first column.
 
|returns a value from a table column, in the row found by lookup in the first column.
  
−
|-valign="top"
 
−
|[[Documentation/How_Tos/Calc: SHEET function|'''SHEET''']]
 
−
|returns the sheet number, given a reference.
 
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: SHEETS function|'''SHEETS''']]
+
|colspan="2"|'''<big> Spreadsheet Information functions</big>'''
−
|returns the number of sheets, given a reference.
+
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: MATCH function|'''MATCH''']]
+
|[[Documentation/How_Tos/Calc: AREAS function|'''AREAS''']]
−
|returns the position in a single row or column table matching a search criterion.
+
|returns the number of individual ranges in a multiple range.
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: OFFSET function|'''OFFSET''']]
+
|[[Documentation/How_Tos/Calc: COLUMN function|'''COLUMN''']]
−
|returns the contents of a cell, given a reference and a desired offset from that reference.
+
|returns the column number, given a reference.
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: LOOKUP function|'''LOOKUP''']]
+
|[[Documentation/How_Tos/Calc: COLUMNS function|'''COLUMNS''']]
−
|returns result from one single-cell-wide table, corresponding to a lookup search in another.
+
|returns the number of columns in a given range.
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: STYLE function|'''STYLE''']]
+
|[[Documentation/How_Tos/Calc: ERRORTYPE function|'''ERRORTYPE''']]
−
|applies a style to a cell (for example a colour).
+
|returns the number corresponding to an error value.
  
 
|-valign="top"
 
|-valign="top"
−
|[[Documentation/How_Tos/Calc: CHOOSE function|'''CHOOSE''']]
+
|[[Documentation/How_Tos/Calc: INFO function|'''INFO''']]
−
|returns a value from a list, given an index number.
+
|returns information about the current working environment.  
−
 
+
−
|-valign="top"
+
−
|[[Documentation/How_Tos/Calc: HLOOKUP function|'''HLOOKUP''']]
+
−
|returns a value from a table row, in the column found by lookup in the first row.
+
  
 
|-valign="top"
 
|-valign="top"
Line 81: Line 74:
 
|-valign="top"
 
|-valign="top"
 
|[[Documentation/How_Tos/Calc: ROWS function|'''ROWS''']]
 
|[[Documentation/How_Tos/Calc: ROWS function|'''ROWS''']]
−
|returns the number of rows  in a given reference.
+
|returns the number of rows  in a given range.
 +
 
 +
|-valign="top"
 +
|[[Documentation/How_Tos/Calc: SHEET function|'''SHEET''']]
 +
|returns the sheet number, given a reference.
 +
 
 +
|-valign="top"
 +
|[[Documentation/How_Tos/Calc: SHEETS function|'''SHEETS''']]
 +
|returns the number of sheets, given a reference.
 +
 
 +
 
 +
|-valign="top"
 +
|colspan="2"|'''<big>Other functions</big>'''
 +
 
 +
|-valign="top"
 +
|[[Documentation/How_Tos/Calc:  DDE function|'''DDE''']]
 +
|returns information from other documents and applications, using the "DDE" protocol.
  
 
|-valign="top"
 
|-valign="top"
 
|[[Documentation/How_Tos/Calc: HYPERLINK function|'''HYPERLINK''']]
 
|[[Documentation/How_Tos/Calc: HYPERLINK function|'''HYPERLINK''']]
 
|sets a cell to open a hyperlink (in another application) when clicked.
 
|sets a cell to open a hyperlink (in another application) when clicked.
 +
 +
|-valign="top"
 +
|[[Documentation/How_Tos/Calc: STYLE function|'''STYLE''']]
 +
|applies a style to a cell (for example a colour).
 +
 
|}
 
|}
 
<br style="clear:both;" />
 
<br style="clear:both;" />

Revision as of 08:45, 25 January 2008

List of Calc 'Spreadsheet' functions

The so-called 'Spreadsheet' functions find values in tables, or cell references; they include LOOKUP, SEARCH, ADDRESS. They could thus also be described as 'Lookup' functions.


Spreadsheet Lookup functions
ADDRESS returns a text representation of a cell reference, given row and column numbers.
CHOOSE returns a value from a list, given an index number.
HLOOKUP returns a value from a table row, in the column found by lookup in the first row.
INDEX returns the contents of a cell, given row and column number.
INDIRECT returns a reference, given a text string.
LOOKUP returns result from one single-cell-wide table, corresponding to a lookup search in another.
MATCH returns the position in a single row or column table matching a search criterion.
OFFSET returns the contents of a cell, given a reference and a desired offset from that reference.
VLOOKUP returns a value from a table column, in the row found by lookup in the first column.


Spreadsheet Information functions
AREAS returns the number of individual ranges in a multiple range.
COLUMN returns the column number, given a reference.
COLUMNS returns the number of columns in a given range.
ERRORTYPE returns the number corresponding to an error value.
INFO returns information about the current working environment.
ROW returns the row number, given a reference.
ROWS returns the number of rows in a given range.
SHEET returns the sheet number, given a reference.
SHEETS returns the number of sheets, given a reference.


Other functions
DDE returns information from other documents and applications, using the "DDE" protocol.
HYPERLINK sets a cell to open a hyperlink (in another application) when clicked.
STYLE applies a style to a cell (for example a colour).


See also

Functions listed by category

Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/How_Tos/Calc:_Spreadsheet_functions&oldid=61610"
Personal tools