Excel 2010 Bible

Part II: Working with Formulas and Functions

358

FIGURE 16.2

The array formula in cell C9 calculates the total sales without using intermediate formulas.

The following array formula is in cell C9:

{=SUM(B2:B7*C2:C7)}

When you enter this formula, make sure that you use Ctrl+Shift+Enter (and don’t type the curly

brackets because Excel automatically adds them for you).

This formula works with two arrays, both of which are stored in cells. The first array is stored in

B2:B7, and the second array is stored in C2:C7. The formula multiplies the corresponding values

in these two arrays and creates a new array (which exists only in memory). The SUM function then

operates on this new array and returns the sum of its values.

Note
In this case, you can use the SUMPRODUCT function to obtain the same result without using an array formula:

=SUMPRODUCT(B2:B7,C2:C7)

As you see, however, array formulas allow many other types of calculations that are otherwise not

possible.

Creating an array constant .......................................................................................

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

this section demonstrate an important concept: An array need not 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 brackets. Here’s an example of a

five-item horizontal array constant:

{1,0,1,0,1}