Page 116 - Excel Workbook for Dummies

11_798452 ch06.qxp 3/13/06 7:48 PM Page 99
Chapter 6: Copying and Correcting Formulas 99

Figure 6-3:
Exercise6-4.
xls work-
book with
the 2006
Spring Sale
sheet active
in the top
window and
the Range
Names
sheet
active in the
bottom one.

Building Array Formulas

Instead of copying a formula to all the cells that perform the same type of calculation,
you can build an array formula that performs the desired calculation not only in the
active cell but in all the other cells to which you would normally copy the formula. An
array formula is a special formula that operates on a range of values. If this range is
supplied by a cell range (as is often the case), it is referred to as an array range. If this
range is supplied by a list of numerical values, it is known as an array constant.

You are already quite familiar with arrays even though you may not realize it. This is
because the Excel worksheet grid with its column-and-row structure naturally organizes
your data ranges into one-dimensional and two-dimensional arrays (one-dimensional
arrays take up a single row or column, whereas two-dimensional arrays take up multi-
ple rows and columns).
Figure 6-4 illustrates a couple of two-dimensional arrays with numerical entries of two
different sizes. The first array is a 3-by-2 array in the cell range B2:C4. This array is a
3-by-2 array because it occupies three rows and two columns. The second array 2-by-
3 array in the cell range B6:D7. This array is a 2-by-3 array because it uses two rows in
three columns. If you were to list the values in the first 3-by-2 array as an array con-
stant in a formula, they would appear as follows:

{1,4;2,5;3,6}

Here are several things of note in this expression:
The array constant is enclosed in a pair of braces ({}).
Columns within each row are separated by commas (,) and rows within the array
are separated by semicolons (;).