Microsoft Office Tutorials and References

In Depth Information

All of the examples in this chapter are available at this book's website. The filename is

array examples.xlsx.

A multicell array formula

Figure 14-1 shows a simple worksheet set up to calculate product sales. Normally, you would calculate the

value in column D (total sales per product) with a formula such as the one that follows and then copy this for-

mula down the column:

=B2*C2

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

Figure 14-1:
Column D contains formulas to calculate the total sales for each product.

Another alternative 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 example, the range is D2:D7.

2.
Enter the following formula, either by typing it or by pointing to the ranges:

=B2:B7*C2:C7

3.
Normally, you press Enter to enter a formula. Because this is an array formula, however, you press

Ctrl+Shift+Enter.