Microsoft Office Tutorials and References

In Depth Information

**Chapter 14: Introducing Arrays**

In this case, you can use Excel’s SUMPRODUCT function to obtain the same result

without using an array formula:

=SUMPRODUCT(B2:B7,C2:C7)

As you’ll see, however, array formulas allow many other types of calculations that are

otherwise not possible.

Creating an array constant

The examples in the previous section used arrays stored in worksheet ranges. The examples in

this section demonstrate an important concept: An array does not have to be stored in a range of

cells. This type of array, which is stored in memory, is referred to as an
array constant.

You create an array constant by listing its items and surrounding them with curly brackets. Here’s

an example of a five-item horizontal array constant:

{1,0,1,0,1}

The following formula uses the SUM function, with the preceding array constant as its argument.

The formula returns the sum of the values in the array (which is 3). Notice that this formula uses

an array, but it is not an array formula. Therefore, you do not use Ctrl+Shift+Enter to enter the

formula.

=SUM({1,0,1,0,1})

When you specify an array directly (as shown previously), you must provide the curly

brackets around the array elements. When you enter an array formula, on the other

hand, you do not supply the curly brackets.

At this point, you probably don’t see any advantage to using an array constant. The formula that

follows, for example, returns the same result as the previous formula:

=SUM(1,0,1,0,1)

Keep reading, and the advantages will become apparent.

Following is a formula that uses two array constants:

=SUM({1,2,3,4}*{5,6,7,8})