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.

Linking workbooks

Creating dynamic links between workbooks using external reference formulas provides a

number of advantages. For example, you could break a large, complex company budget

model into more manageable departmental models. Then you could link all the

departmental workbooks (supporting workbooks) to a master budget workbook (a dependent