Microsoft Office Tutorials and References
In Depth Information
Creating named formulas
Creating named formulas
If you created a chart from the data in columns D:E, the result would not be very
satisfactory. Every nth value would be plotted, but the chart would display a
lengthy series of empty (#NA) cells.
The solution is to call upon the named formulas technique to substitute for the
two array formulas. These named formulas are identical to the array formulas listed
in the previous section.
Define NewDates as
=N(OFFSET(Dates,(ROW(OFFSET(Sheet2!$A$1,0,0,ROWS(Dates)/Nth))-1)*Nth,0))
Define NewData as
=N(OFFSET(Data,(ROW(OFFSET(Sheet2!$A$1,0,0,ROWS(Data)/Nth))-1)*Nth,0))
After you create the names, you use these two named formulas for the category
labels and values range for the chart series. For more information about using named
formulas for a chart series, refer to “Modifying the Series,” earlier in this chapter. The
result? The arrays used by the charts consist only of the values (no #NA values).
Because the named formulas substitute for the array formulas, the formulas in
columns D:E are no longer needed.
Plotting a Series Based
on the Active Cell
Another type of interactive chart is one that plots a series based on the user’s
selection in the worksheet. Figure 7-13 shows an example. This worksheet contains the
results of a customer survey and is set up such that the chart displays the data in
the row that contains the cell pointer. When you press F9 (to calculate the
workbook), a named formula is calculated, and that named formula is used in the chart’s
SERIES formula.
The CD-ROM contains two versions of this example. The version described in
the preceding paragraph requires a press of the F9 key to update the chart.
The other version uses a simple event macro that is executed whenever the
selection changes. The macro-driven version is completely automatic.
Search JabSto ::




Custom Search