Microsoft Office Tutorials and References

In Depth Information

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.

Because displaying more than one value in a single cell is not possible, six cells are required to display the res-

ulting 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 indi-

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

• It's a good way of ensuring that all formulas in a range are identical.

• Using a multicell array formula makes it less likely that you will overwrite a formula accidentally. You can-

not change or delete one cell in a multicell array formula.

• Using a multicell array formula will almost certainly prevent novices from tampering with your formulas.

As you see later, multicell array formulas can be more useful than this trivial introductory example.

A single-cell array formula

Now take a look at a single-cell array formula. See Figure 14-2. The following array formula is in cell C9:

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