Microsoft Office Tutorials and References
In Depth Information
Using array formulas
whether the row should be hidden. The formula in cell C4, which is copied to the
cells below, is
=MOD(ROW()-ROW($B$4),$B$1)
This formula uses the MOD function to calculate the remainder when the row
number (minus the row number of the first row) is divided by the value in B1. As a
result, every nth cell contains 0.
Figure 7-11: This chart plots every nth data point (specified in B1) by ignoring data
in the rows hidden by AutoFiltering.
AutoFilter command to turn on AutoFiltering. Use the
drop-down arrow in cell C3 to display only the rows that contain a 0 in column C.
This technique will not work if the Plot Visible Cells Only option is not in effect for
the chart. By default, this setting is in effect. To check (or change) this setting, select
the chart, choose Tools
Use the Data
Filter
Options, and click the Chart tab.
The main problem with this technique is that it’s not fully automatic. When
you change the value in cell B1, you need to re-specify the AutoFilter criteria
for column C. The rows will not hide automatically.
Using array formulas
The preceding technique works well, but it would be nice to make it fully
automated. Tushar Mehta, an Excel charting expert, developed a clever technique that
uses named formulas. The example in this section is an adaptation of his method.
Search JabSto ::




Custom Search