Microsoft Office Tutorials and References
In Depth Information
Chapter 14: Introducing Arrays
Because displaying more than one value in a single cell is not possible, six cells are required to
display the resulting array. That explains why you selected six cells before you entered the array
This array formula, of course, returns exactly the same values as these six normal formulas
entered into individual cells in D2:D7:
Using a single array formula rather than individual formulas does offer a few advantages:
h It’s a good way of ensuring that all formulas in a range are identical.
h Using a multicell array formula makes it less likely that you will overwrite a formula
accidentally. You cannot change one cell in a multicell array formula.
h Using a multicell array formula will almost certainly prevent novices from tampering with
A single-cell array formula
Now it’s time to take a look at a single-cell array formula. Refer again to Figure 14-1. The
following array formula occupies a single cell:
You can enter this formula into any cell. Remember: When you enter this formula, make sure you
press Ctrl+Shift+Enter (and don’t type the curly brackets).
This array formula returns the sum of the total product sales. It’s important to understand that
this formula does not rely on the information in column D. In fact, you can delete column D, and
the formula will still work.
This formula works with two arrays, both of which are stored in cells. The first array is stored in
B2:B7, and the second array is stored in C2:C7. The formula multiplies the corresponding values
in these two arrays and creates a new array (which exists only in memory). The SUM function
then operates on this new array and returns the sum of its values.