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

Custom Search