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