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
Search JabSto ::




Custom Search