Microsoft Office Tutorials and References

In Depth Information

**ABOUT THE OFFSET FUNCTION**

Normally, cell and range addresses used in named formulas are absolute

addresses — the row and column references are preceded by a dollar sign. If

you use standard relative addresses, the result returned by the named

formula will vary, depending on the location of the cell pointer. As you’ll see

later in this chapter, you can use this to your benefit (see “Plotting a Series

Based on the Active Cell”).

ABOUT THE OFFSET FUNCTION

The key to mastering self-expanding charts is understanding the OFFSET function.

This function returns a range that is “offset” from a specified reference cell.

Arguments for the OFFSET function let you specify the distance from the reference

cell and the dimensions of the range (the number of rows and columns).

The OFFSET function has five arguments, as follows:

reference:
The first argument for the OFFSET function is essentially the

“anchor” cell, used by the second and third argument.

rows:
This argument indicates how many rows to move from the reference

address to begin the range.

cols:
This argument indicates how many columns to move from the

reference address to begin the range.

height:
This argument indicates the number of rows to be included in the

range.

width:
The final argument indicates the number of columns to be included

in the range.

If the columns used for the data contain any other entries, COUNTA will

return an incorrect value. To keep things simple, don’t put any other data in

the column. If the column contains additional information, you’ll need to

adjust the
height
argument in the COUNTA function.

Recall that the named formula
Sales
was defined as

=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)

If there are 11 entries in column B, the COUNTA function returns 11. This result

is adjusted by one to account for the column heading. Therefore, the named

formula can be expressed as

=OFFSET(Sheet1!$B$2,0,0,10,1)