|
The COUNT function and how the dynamic name works
Take a closer look at the formula for the name ChartDate.
| =OFFSET(Cdate,1,0):OFFSET(Cdate,COUNT($A:$A),0) |
We defined two OFFSET functions, separated by a colon, to signify
a range. One of the arguments in the second OFFSET function
is the COUNT function, which is what makes the formula dynamic.
The COUNT function counts how many numeric entries are in a range
of cells:
In this case, it takes a range of cells as an argument. The argument
is the entire column A, expressed as $A:$A. The COUNT function
calculates only the total number of numeric entries in column
A; it does not count the text entries in cells A1 and A2. When
another date entry is added to the bottom of the list, the number
returned by the COUNT function will increase by 1.
The OFFSET function returns a range of cells that is offset from
another cell reference. It takes a cell reference, a row number,
and a column as arguments:
| =OFFSET(Reference, Row, Column) |
In the first OFFSET function, OFFSET(Cdate,1,0), the reference
argument is Cdate (i.e., the name we assigned to cell
A2). The row argument is 1, the cell that is one row below the
reference argument, which in our case refers to cell A3. The
column argument is 0, which is the number of columns to the
right of the reference argument (A3). In the second OFFSET function,
OFFSET(Cdate,COUNT($A:$A),0), the reference argument is also
Cdate. The row argument is COUNT($A:$A). The COUNT function
returns the number of date entries in Column A. If there were
10 date entries in column A, the COUNT function would return
the number 10 and the row argument would refer to cell A12,
which is 10 rows below Cdate (A2). The column argument is 0.
By defining the name ChartDate in this way, the range
of cells to which ChartDate refers will change as date
entries are added to Column A. The same dynamics apply to the
name Yld30. The range of cells to which Yld30 refers will change
as additional yield data is added in Column B.E
|
