Excel 2010 Bible

Part II: Working with Formulas and Functions

356

This section presents two array formula examples: an array formula that occupies multiple cells

and another array formula that occupies only one cell.

A multicell array formula.........................................................................................

Figure 16.1 shows a simple worksheet set up to calculate product sales. Normally, you’d calculate

the value in column D (total sales per product) with a formula such as the one that follows, and

then you’d copy this formula down the column.

=B2*C2

After copying the formula, the worksheet contains six formulas in column D.

FIGURE 16.1

Column D contains formulas to calculate the total for each product.

An alternative method uses a single formula (an array formula) to calculate all six values in D2:D7.

This single formula occupies six cells and returns an array of six values.

To create a single array formula to perform the calculations, follow these steps:

1. Select a range to hold the results. In this case, the range is D2:D7. Because you can’t
   display more than one value in a single cell, six cells are required to display the resulting
   array — so you select six cells to make this array work.


2. Type the following formula:

=B2:B7*C2:C7

3. Press Ctrl+Shift+Enter to enter the formula. Normally, you press Enter to enter a for-
   mula. Because this is an array formula, however, press Ctrl+Shift+Enter.

Caution
You can’t insert a multicell array formula into a range that has been designated a table (using Insert ➪
Tables ➪ Table). In addition, you can’t convert a range that contains a multicell array formula to a table. n