OFFSET(reference;rows;columns;height;width)
function
Returns a crossreference of the cells situated from
a reference (ref) a few rows below (nrows positive), or above (nrows negative),
a few columns to the right (ncolumns positive), or more columns to the
left (ncolumns negative). The height and width parametres indicate the
number of cells that need to be recovered as from that point.
Example: =OFFSET(A1;2;3)
returns the value situated 2 rows below, and 3 columns to the
right of cell A, that is, in cell D3.
=SUM(OFFSET(K4;3;1;3;2))
obtains the sum of the cells returned by the OFFSET function. In this
case the desref function returns the J1:K3 cells, to see this we look
at how to perform the function: from cell K4 we move 3 rows up (nfiles3),
and 1 column to the left (ncolumns1), we reach cell J1, as from this
cell we take 3 rows (height 3) and 2 columns (width 2), that is cells
J1,J2,J3,K1,K2 AND K3, the desref function has returned to us the range
J1:K3.
ADDRESS(row_num,column_num,abs_num,a1,sheet_text)
function
Creates a cell address as text, given specified row
and column numbers.
Column_num is the column number to
use in the cell reference.
Row_num is the row number to use
in the cell reference.
abs_num specify the type of reference
to be returned.
( 1 or omit returning the absolute reference
2 returns an absolute reference row, relative column.
3 returns a relative reference row, absolute column.
4 returns a relative reference
a1 is a logical value that specifies
the style of the A1 or F1C1 reference.If a1 is TRUE or is omitted, ADDRESS
returns a a reference of the B3 style; if it's FALSE, ADDRESS will return
a reference of the F3C2 style (Row3Column2).
Sheet is text that specifies the
name of the calculation sheet or that is used as an external reference.
If sheet is omitted, then no sheet name will be used.
Example: =ADDRESS(1;2)
returns an absolute reference to ($B$1)
=ADDRESS(1;2;4) returns
an absolute reference to (B1)
=ADDRESS(1;2;4;false) returns
an absolute reference to (F1C2)
CHOOSE
(index_num;value1;value2;...) function
Choose a value or an action from a list of value's
using an index number.
Example: CHOOSE(3;"grape";"pear";"melon";"apple")
returns "melon" which is in the 3rd position.
ROW
(ref) function
Returns the row number of a reference.
Example:
=ROW(A2:B5) returns 2, as the cell A2 is in the 2nd row.
ROWS
(array) function
Returns the number of rows in a reference or array.
Example: =ROWS(A2;B5)
returns 4
HYPERLINK
(link_location;friendly_name) function
Create a direct access to a document saved in the
hard drive or on the internet.
Example: =HYPERLINK("HTTP;www.teacherclick.com";"Teacherclick")
creates a link to our Web page.
GETPIVOTDATA
(data_field;pivot_table;field1;element1;field2;element2...)
function
Extracts data stored in a pivot table.
INDEX
(matrix;row_num;column_num) function
Within a range specified by way of matrix, returns
the value of the cell found at the intersection of a specific row and
column.
Example: =INDEX(A3:b7;2;1)
returns the value of the cell found in the second row of the matrix
and in the first column, that is, A4.
INDIRECT
(ref_text;a1) function
Returns the reference specified by a text value.
Example:
=INDIRECT (A2) returns the link value of the cell A2 which is also
a link. Let us imagine that in cell B5 we have a value of 7 and in the
cell A2 we have put B5, if we write the function =INDIRECT(A2) it returns
the value 7.
TRANSPOSE
(array) function
Returns a vertical range of cells as a horizontal
range, or vice versa
We have a row with the values 4 6 7 9, and with achieving
this function.
Example: =TRANSPOSE(($A$1:$D$1)
we will obtain the value 4 in a row, the value 6 in the next row of the
same column etc.... as a result.
Note: The formula of the example
must be introduced as a formula. We first need to select the A2:A5 range
starting with the formula's cell, pressing F2, and next CTRL+SHIFT+ENTER.
If the formula is not introduced as a formula, the only result is 1.
