Excel 2010 Bible

Part II: Working with Formulas and Functions

368

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:

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

Figure 16.10 shows the result when you enter this formula into a range:

FIGURE 16.10

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. Figure 16.11 shows the

result when you enter this formula into a range:

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

If the array is stored in a range (such asB8:E10), the array formula returns the square of each

value in the range, as follows:

{=B8:E10^2}

FIGURE 16.11

Multiplying each array element by itself.