Functions that search for a value (II)

OFFSET(reference;rows;columns;height;width) function

Returns a cross-reference 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 (nfiles-3), and 1 column to the left (ncolumns-1), 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.

 



 

 

Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited.
November-2005.