Microsoft Office Tutorials and References

In Depth Information

**Using Multicell Array Formulas**

{={1,2,3,4;5,6,7,8;9,10,11,12}*2}

Figure 14-9 shows the result when you enter this formula into a range:

Figure 14-9:
Performing a mathematical operation on an array.

The following array formula multiplies each array element by itself:

{={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}}

The following array formula is a simpler way of obtaining the same result:

{={1,2,3,4;5,6,7,8;9,10,11,12}^2}

Figure 14-10 shows the result when you enter this formula into a range.

If the array is stored in a range (such as A1:C4), the array formula returns the square of each

value in the range, as follows:

{=A1:C4^2}

Figure 14-10:
Multiplying each array element by itself.

In some of these examples are brackets that you must enter to define an array constant

as well as brackets that Excel enters when you define an array by pressing Ctrl+Shift+

Enter. An easy way to tell whether you must enter the brackets is to note the position

of the opening curly bracket. If it’s before the equal sign, Excel enters the bracket. If it’s

after the equal sign, you enter them.