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




Custom Search