Microsoft Office Tutorials and References

In Depth Information

**Chapter 14: Introducing Arrays**

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 formula 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:

=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.

The formula is entered into all six selected cells. If you examine the Formula bar, you’ll see the

following:

{=B2:B7*C2:C7}

Excel places curly brackets around the formula to indicate that it’s an array formula.

This formula performs its calculations and returns a six-item array. The array formula actually

works with two other arrays, both of which happen to be stored in ranges. The values for the first

array are stored in B2:B7, and the values for the second array are stored in C2:C7.