Microsoft Office Tutorials and References

In Depth Information

**Using array formulas**

Figure 7-12 shows the same data used in the previous example. This workbook

uses three named ranges:
Nth
(cell B1),
Dates
(range A4:A368), and
Data
(range

B4:B368). The
Nth
cell is linked to a Spinner control.

Figure 7-12: Using named formulas to return every nth data point

You’ll notice two additional columns of formulas (columns D and E). Both of

these are multicell array formulas. The array formula in D4:D368 is

=N(OFFSET(Dates,(ROW(OFFSET($A$1,0,0,ROWS(Dates)/Nth))-1)*Nth,0))

This formula returns an array that consists of every
nth row in the
Dates
range.

The array formula in E4:E368 is very similar and returns an array that consists

of every
nth row in the
Data
range:

=N(OFFSET(Data,(ROW(OFFSET($A$1,0,0,ROWS(Data)/Nth))-1)*Nth,0))

These array formulas are complex, and a complete explanation is beyond

the scope of this topic. However, it’s not necessary that you fully understand

them in order to use them. They can easily be adapted to other data sets. Be

aware that the reference to cell $A$1 must remain intact. This cell is used to

generate a series of offsets that reference cells within the original range.