Page 153 - Excel Timesaving Techniques for Dummies

29_574272 ch25.qxd 10/1/04 10:47 PM Page 138
138
Technique 25: Speeding Up Table Creation with Array Formulas
Hurray for Array Formulas!

Array formulas are easy to build. Just keep in mind
that you’re creating computations that use complete
ranges of cells rather than just individual cells.
Because you want to insert the array formula into an
entire range of cells rather than just the current one,
you can’t complete the formula entry either by click-
ing the Enter button on the Formula bar or by press-
ing the Enter key. Instead, you must complete the
• Figure 25-1: Sample one- and two-dimensional array
entry by pressing Ctrl+Shift+Enter. This is the only, I
ranges.
repeat, only way to enter the array formula in all the
cells of the array range.
The first one-dimensional array in this figure is
located in the cell range B3:D3. This array range is Figure 25-2 illustrates a situation where you can
a 1 x 3 range (one row across three columns) with use array formulas to good advantage. This figure
three array constants. In an array formula, these contains a table that tracks wages for a group of
constants would appear as: employees in the month of February. These wages
are calculated for two periods — the 1st to the 15th
{1,2,3}
and the 16th to the 28th — and then summed to pro-
duce monthly totals. To compute the wages for each
The second one-dimensional array is in the cell
period, you need to multiply the hours worked by the
range F2:F4. This array range is a 3 x 1 range (three
employee in that period by his or her hourly wage.
rows in one column) with three array constants. In
(Note that, in this figure, columns C through Q and
an array formula, these constants would appear as:
columns S through AH — the columns that record the
daily hours worked by each employee within each
{4;5;6}
pay period — are hidden for the purpose of creating
The first two-dimensional array in Figure 25-1 is in the array formulas that calculate their totals.)
the cell range B6:D7. This array range is a 2 x 3 range
(two rows in three columns) with six array constants.
In an array formula, these constants would appear as:
{7,8,9:10,11,12}
The second two-dimensional array is in the cell
range F6:G8. This array range is a 3 x 2 range (three
rows in two columns) with six array constants. In an
array formula, these constants would appear as:
{13,14;15,16;17,18}

When listing the contents of an array range,
the entries are listed across columns and then
by rows. Commas are used to separate the
columns of a row, and semicolons separate the
• Figure 25-2: A worksheet for computing wages that can
rows. The entire contents list is enclosed in a benefit from array formulas.
closed pair of curly braces.