Microsoft Office Tutorials and References

In Depth Information

**Defining the names**

Defining the names

In this example, cell F1 is named
NumMonths
. The workbook has two other named

formulas.
Date
is defined as

=OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$B:$B)-NumMonths-1,0,NumMonths,2)

Sales
is defined as

=OFFSET(Sheet1!$C$2,COUNTA(Sheet1!$C:$C)-NumMonths-1,0,NumMonths,1)

The chart title uses a link to cell E6, which contains the following formula:

=”Sales by Month (Last “ & NumMonths &” Months)”

This formula uses the cell name
NumMonths
to ensure that the chart title always

displays the number of months plotted.

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 number of data points in the chart will then be controlled by the value

in cell F1. New data added to the worksheet will be accommodated automatically.

Plotting Every nth Data

Point in a Series

Suppose that you have a lot of data in a column, and you want to plot only every

10th data point. This section presents two techniques that enable you to do just that.

Using AutoFiltering

One way to plot every
nth data point in a range is to use AutoFiltering in

conjunction with a formula. AutoFiltering allows you to hide rows that don’t meet a

specified criteria. Excel, by default, doesn’t plot data that resides in a hidden row.

Therefore, the trick is to create formulas that return a specific value based on the

data’s row number and then use the results of these formulas as the basis for

AutoFiltering.

Figure 7-11 shows a worksheet with AutoFiltering in effect. Cell B1 contains a

value that represents
n.
For example, when B1 contains 10, the chart displays every

10th data point: the value in rows 4, 14, 24, and so on.

Column A contains 365 dates and column B contains 365 corresponding data

points. Column C contains formulas that return a value which is used to determine