Microsoft Office Tutorials and References

In Depth Information

**Using Multicell Array Formulas**

Figure 14-11:
Using the TRANSPOSE function to transpose a rectangular array.

You can, of course, use the TRANSPOSE function to transpose an array stored in a range. The

following formula, for example, uses an array stored in A1:C4 (four rows, three 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

As you will see in Chapter 15, it’s often useful to generate an array of consecutive integers for use

in an array formula. Excel’s 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 demonstrate,

select a range that consists of 12 rows and 1 column, and then enter the array formula into the

range. You’ll find that the range is filled with 12 consecutive integers (see Figure 14-12).

Figure 14-12:
Using an array formula to generate consecutive integers.