Page 117 - Excel Workbook for Dummies

11_798452 ch06.qxp 3/13/06 7:48 PM Page 100
100 Part II: Using Formulas and Functions
Constants in the array are listed across each row and then down each column,
not down each column and across each row.

The second 2-by-3 array expressed as an array constant appears as follows:
{7,8,9;10,11,12}

Note again that you list the values across each row and then down each column, sepa-
rating the values in different columns with commas and the values in different rows
with a semicolon.

Figure 6-4:
Worksheet
containing a
3-by-2 in the
cell range
B2:C4 and
a 2-by-3
array in the
cell range
B6:D7.

The use of array formulas can significantly reduce the amount of formula copying that
you have to do in a worksheet by producing multiple results throughout the array range
in a single operation. In addition, array formulas use less computer memory than stan-
dard formulas copied in a range. This can be important when creating a large worksheet
with many tables as it may mean the difference between fitting all of your calculations
on one worksheet or having to split your model into several worksheet files.

Try It

Exercise 6-5: Constructing Array Formulas
Open the Exercise6-5.xls file in your Chapter 6 folder inside the My Practice Spreadsheets
folder or on the Excel Workbook CD-ROM. This file contains a copy of the February 2006
Hourly Wage spreadsheet that you can use to practice constructing array formulas. This
worksheet is designed to compute the biweekly wages for each employee by multiplying
the hourly rate by the number of hours worked. Rather than create an original formula
for each period that computes the wages for the first employee and then copying it
down rows for all the others, do this with array formulas: