Microsoft Office Tutorials and References
In Depth Information
Creating the chart
Creating the chart
The first step is to create a standard chart, using the data that currently exists.
Figure 7-1, presented earlier, shows the data and a column chart created from the
data. The chart contains a single series and its SERIES formula is as follows:
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)
This SERIES formula specifies that:
The series name is in cell B1.
The category labels are in A2:A11.
The values are in B2:B11.
So far, this is just a common chart. If you add a new date and value, the chart
will not display the new data. But that will soon change.
Creating named formulas
In this step, you create two named formulas. The names will eventually serve as
arguments in the SERIES formula. In case you’re not familiar with the concept of a
named formula, it is explained later in this section. To create the named formulas:
1. Select Insert
Name
Define to bring up the Define Name dialog box.
2. In the Names in workbook field, enter Date . In the Refers to field, enter
this formula:
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
3. Click Add to create the formula named Date .
Notice that the OFFSET function refers to the first category label (cell A2)
and uses the COUNTA function to determine the number of labels in the
column. Because column A has a heading in row 1, the formula subtracts
1 from the number.
4. Type Sales in the Names in workbook field. Enter this formula in the
Refers to field:
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)
In this case, the OFFSET function refers to the first data point (cell B2).
Again, the COUNTA function is used to get the number of data points, and
it is adjusted to account for the label in cell B1.
5. Click Add to create the formula named Sales .
6. Click Close to close the Define Name dialog box.
Search JabSto ::




Custom Search