Microsoft Office Tutorials and References

In Depth Information

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 ar-

ray 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 ex-

ample, 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})

This formula creates a new array (in memory) that consists of the product of the corresponding elements in the

two arrays. The new array is as follows:

{5,12,21,32}

This new array is then used as an argument for the SUM function, which returns the result (70). The formula is

equivalent to the following formula, which doesn't use arrays:

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