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})}
Search JabSto ::

Custom Search