Microsoft Office Tutorials and References
In Depth Information
Using Multicell Array Formulas
Figure 14-7: Creating an array from a range.
The array in D8:F11 is linked to the range A1:C4. Change any value in A1:C4, and the
corresponding cell in D8:F11 reflects that change.
Creating an array constant from values in a range
In the previous example, the array formula in D8:F11 essentially created a link to the cells in A1:C4.
It’s possible to sever this link and create an array constant made up of the values in A1:C4.
To do so, select the cells that contain the array formula (the range D8:F11, in this example). Then
press F2 to edit the array formula. Press F9 to convert the cell references to values. Press
Ctrl+Shift+Enter to reenter the array formula (which now uses an array constant). The array
constant is as follows:
Figure 14-8 shows how this looks in the Formula bar.
Figure 14-8: After you press F9, the Formula bar displays the array constant.
Performing operations on an array
So far, most of the examples in this chapter simply entered arrays into ranges. The following
array formula creates a rectangular array and multiplies each array element by 2: