Microsoft Office Tutorials and References
In Depth Information
Understanding array expansion
To enter a formula using an array constant, follow these steps:
Select a range of cells the size you need to contain the result. In Figure 12-35, the
argument to the INT function contains three groups (separated by semicolons) of
three values (separated by commas), which produces a three-row, three-column
range.
1.
Enter an equal sign to begin the formula and, optionally, a function name and
opening parenthesis.
2.
Type the array argument enclosed in braces to indicate that the enclosed values
make up an array constant. If you entered a function, type its closing parenthesis.
3.
Press Ctrl+Shift+Enter. The resulting array formula contains two sets of curly bracesâ€”
one set encloses the array constant, and the other encloses the entire array formula.
4.
When entering array constants, remember that commas between array elements place
those elements in separate columns, and semicolons between array elements place those
elements in separate rows.
Understanding array expansion
When you use arrays as arguments in a formula, all your arrays should have the same
dimensions. If the dimensions of your array arguments or array ranges do not match,
Excel often expands the arguments for you. For example, to multiply all the values in
cells A1:B5 by 10, you can use either of the following array formulas: { =A1:B5*10} or
{ ={ 1,2;3,4;5,6;7,8;9,10}*10}.
Note that neither of these two formulas are balanced; ten values are on the left side of
the multiplication operator, but only one is on the right. Excel expands the second
argument to match the size and shape of the first. In the preceding example, the first formula
is equivalent to { =A1:B5*{ 10,10;10,10;10,10;10,10;10,10} }, and the second is equivalent to
{ ={ 1,2;3,4;5,6;7,8;9,10}*{ 10,10;10,10;10,10;10,10;10,10} }.
When you work with two or more sets of multivalue arrays, each set must have the same
number of rows as the argument with the greatest number of rows, and each must have the
same number of columns as the argument with the greatest number of columns.