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:
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.
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:
Keep reading, and the advantages will become apparent.
Following is a formula that uses two array constants:
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:
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: