Chapter 16: Introducing Array Formulas
369
Using functions with an array ..................................................................................
As you may expect, you also can use worksheet functions with an array. The following array for-
mula, 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})}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 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 16.12 shows a two-
dimensional array entered into a range normally and entered into a range by 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})}You can, of course, use the TRANSPOSE function to transpose an array stored in a range. The fol-
lowing 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)}