Microsoft Office Tutorials and References
In Depth Information
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”).
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
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)
Search JabSto ::

Custom Search