Microsoft Office Tutorials and References

In Depth Information

The example in this section, named plot every nth data point.xlsx, is available at this

book's website.

Cell A1 contains the value 10. The value in this cell determines which rows to hide. Column B contains identic-

al formulas that use the value in cell A1. For example, the formula in cell B4 is as follows:

=MOD(ROW()–ROW($A$4),$A$1)

Figure 17-11:
This chart plots every
n
th data point (specified in A1) by ignoring data in the rows hidden by fil-

tering.

This formula subtracts the current row number from the first data row number in the table and uses the MOD

function to calculate the remainder when that value is divided by the value in A1. As a result, every
n
th cell (be-

ginning with row 4) contains 0. Use the filter drop-down list in cell B3 to specify a filter that shows only the

rows that contain a 0 in column B.