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:

{=ROW(2:13)}

The formula that originally generated integers from 1 to 12 now generates integers from 2 to 13.

For a better solution, use this formula:

{=ROW(INDIRECT(“1:12”))}

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.