Microsoft Office Tutorials and References

In Depth Information

**Using Multicell Array Formulas**

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, an 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-11 shows a

twodimensional 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})}