Page 368 - Excel 2007 Bible

22_044039 ch17.qxp 11/21/06 11:08 AM Page 325
FIGURE 17.11
Multiplying each array element by itself.
Using functions with an array
As you may expect, you also can use functions with an array. The following array formula, which you can
enter into a 10-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})} Introducing Array Formulas 17
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 the 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 17.12 shows a two-dimensional
array entered into a range normally and entered into a range by using the TRANSPOSE function. The for-
mula 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})}

325