Microsoft Office Tutorials and References

In Depth Information

Figure 14-11:
Multiplying each array element by itself.

Using functions with an array

As you might expect, you also can use functions with an array. The following array formula, which you can

enter into a ten-cell vertical range, calculates the square root of each array element in the array constant:

{=SQRT({1;2;3;4;5;6;7;8;9;10})}

If the array is stored in a range, a multicell array formula such as the one that follows returns the square root of

each value in the range:

{=SQRT(A1:A10)}

Transposing an array

When you transpose an array, you essentially convert rows to columns and columns to rows. In other words,

you can convert a horizontal array to a vertical array and vice versa. Use Excel's TRANSPOSE function to

transpose an array.

Consider the following one-dimensional horizontal array constant:

{1,2,3,4,5}

You can enter this array into a vertical range of cells by using the TRANSPOSE function. To do so, select a

range of five cells that occupy five rows and one column. Then enter the following formula and press

Ctrl+Shift+Enter:

=TRANSPOSE({1,2,3,4,5})

The horizontal array is transposed, and the array elements appear in the vertical range.

Transposing a two-dimensional array works in a similar manner. Figure 14-12 shows a two-dimensional array

entered into a range normally and entered into a range using the TRANSPOSE function. The formula in A1:D3

is

{={1,2,3,4;5,6,7,8;9,10,11,12}}

The formula in A6:C9 is

{=TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})}