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:

{1,”dog”,3;4,5,”cat”;7,FALSE,9;”monkey”,8,12}

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: