Microsoft Office Tutorials and References
In Depth Information
Using Numbers with OFFSET to Describe a Range
=COLUMN(INDEX(MyAreas,,,B$15)) in cell B16 to
define the starting column of area 1 of MyAreas.
55. Copy the formula from step 4 to B17:B20. Edit each function to change
COLUMN to ROW, COLUMNS, ROWS, and AREAS.
66. Copy B17:B20 to columns C, D, and E.
The result, as shown in Figure 12.35 , includes four sets of formulas in
B16:E20 that completely describe the four areas of the named range
MyAreas.
44. Enter the formula =COLUMN(INDEX(MyAreas,,,B$15))
Using Numbers with
Using Numbers with OFFSET
to Describe a Range
The language of Excel is numbers. There are functions that count the number
of entries in a range. There are functions that can tell you the numeric pos-
ition of a looked-up value. You might know that a particular value is found
in row 20, but what if you want to perform calculations on other cells in row
20?
The OFFSET function handles this very situation. You can use OFFSET to
describe a range using mostly numbers. OFFSET is flexible: It can describe a
single cell, or it can describe a rectangular range.
Although INDEX can return a single cell, row, or column from a rectangular
range, it has limitations. If you specify C5:Z99 as the range for an INDEX
function, you can select only cells below and/or to the right of C5. The
OFFSET function can move up and down or left and right from the starting
cell, which is C5.
OFFSET to Describe a Range
Syntax
OFFSET(reference,rows,cols,height,width)
The OFFSET function returns a reference to a range that is a given number of
rows and columns from a given reference. This function takes the following
arguments:
reference
reference This is the reference from which you want to base the
offset. referencemust be a reference to a cell or range of adjacent
cells; otherwise, OFFSET returns a #VALUE! error.
rows
rows This is the number of rows, up or down, that you want the
upper-left cell to refer to. Using 5 as the rowsargument, for example,
specifies that the upper-left cell in the reference is five rows below
Search JabSto ::




Custom Search