Part II: Working with Formulas and Functions
Single-cell array formulas Work with arrays stored in ranges or in memory and produce a
result displayed in a single cell.
Multicell array formulas Work with arrays stored in ranges or in memory and produce an
array as a result. Because a cell can hold only one value, a multicell array formula is entered
into a range of cells.
This section presents two array formula examples: one that occupies multiple cells and
another that occupies only one cell.
A multicell array formula
Figure 18.1 shows a simple worksheet set up to calculate product sales. Normally, you’d cal-
culate 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
FIGURE 18.1
Column D contains formulas to calculate the total for each product.
After you copy the formula, the worksheet contains six formulas in column D.
An alternative method uses a single formula (a multicell 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 multicell array formula to perform the calculations, follow these steps:
- 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, you select six cells to make this
array work.
- Type the following formula:
=B2:B7*C2:C7
- Press Ctrl+Shift+Enter to enter the formula. Normally, you press Enter to enter a
formula. Because this is an array formula, however, press Ctrl+Shift+Enter.
