Microsoft Office Tutorials and References

In Depth Information

Plotting the last n data points

You can use a technique that makes your chart show only the most recent data points in a column. For example,

you can create a chart that always displays the most recent six months of data. Figure 17-30 shows a worksheet

set up so the user can specify the number of data points.

Figure 17-30:
This chart displays the most recent data points.

The workbook uses three names.
N
is the name for cell E3, which holds the number of data points to plot.

MonthRange
is a dynamic named formula, defined as

=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)–Sheet1!N,0,Sheet1!N,1)

SalesRange is a dynamic named formula, defined as

=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)–Sheet1!N,0,Sheet1!N,1)

The chart's SERIES formula uses the two named formulas: