Chapter 18: Understanding and Using Array Formulas
18
Creating an Array Constant
The examples in the preceding section used arrays stored in worksheet ranges. The exam-
ples in this section demonstrate an important concept: an array doesn’t 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.To create an array constant, list its items and surround 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 ):
=SUM({1,0,1,0,1})Notice that this formula uses an array, but the formula itself isn’t an array formula.
Therefore, you don’t press Ctrl+Shift+Enter to enter the formula—although entering it as an
array formula will still produce the same result.When you specify an array directly (as shown previously), you must provide the curly brackets around the array ele-
ments. 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 follow-
ing formula, for example, returns the same result as the previous formula. The advantages,
however, will become apparent:
=SUM(1,0,1,0,1)Here’s a formula that uses two array constants:
=SUM({1,2,3,4}*{5,6,7,8})The formula creates a new array (in memory) that consists of the product of the corre-
sponding 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)Alternatively, you can use the SUMPRODUCT function. The formula that follows is not an
array formula, but it uses two array constants as its arguments:=SUMPRODUCT({1,2,3,4},{5,6,7,8})