Microsoft Office Tutorials and References
In Depth Information
Figure 14-13: Using an array formula to generate consecutive integers.
If you want to generate an array of consecutive integers, a formula like the one shown previously is good — but
not perfect. To see the problem, insert a new row above the range that contains the array formula. You'll find
that Excel adjusts the row references so the array formula now reads:
The formula that originally generated integers from 1 to 12 now generates integers from 2 to 13.
For a better solution, use this formula:
Worksheet functions that return an array
Several of Excel's worksheet functions use arrays; you must enter a formula that uses one of these functions into
multiple cells as an array formula. These functions are as follows: FORECAST, FREQUENCY, GROWTH,
LINEST, LOGEST, MINVERSE, MMULT, and TREND. Consult the Help system for more information.