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

formula.

This array formula, of course, returns exactly the same values as these six normal formulas

entered into individual cells in D2:D7:

=B2*C2

=B3*C3

=B4*C4

=B5*C5

=B6*C6

=B7*C7

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

your formulas.

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:

{=SUM(B2:B7*C2:C7)}

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.