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

Custom Search