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