Chapter 18: Understanding and Using Array Formulas
18
columns). You can enter this array formula into a range that consists of three rows and four
columns:
{=TRANSPOSE(A1:C4)}Generating an array of consecutive integers
Generating an array of consecutive integers for use in a complex array formula is often
useful. The ROW function, which returns a row number, is ideal for this. Consider the array
formula shown here, entered into a vertical range of 12 cells:
{=ROW(1:12)}This formula generates a 12-element array that contains integers from 1 to 12. To demon-
strate, select a range that consists of 12 rows and 1 column and enter the array formula
into the range. You’ll find that the range is filled with 12 consecutive integers (as shown
in Figure 18.13).
FIGURE 18.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 pre-
viously is good—but not perfect. To see the problem, insert a new row above the range that
contains the array formula. Excel adjusts the row references so that the array formula now
reads as follows:
{=ROW(2:13)}