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.