Page 356 - Excel 2007 Bible

22_044039 ch17.qxp 11/21/06 11:08 AM Page 313
Introducing Array Formulas
ne of Excel’s most interesting (and most powerful) features is its ability to
work with arrays in formulas. When you understand this concept, you’ll IN THIS CHAPTER
Obe able to create elegant formulas that appear to perform spreadsheet The definition of an array and an
magic.
array formula
This chapter introduces the concept of arrays and is required reading for anyone
who wants to become a master of Excel formulas. Chapter 18 continues with lots One-dimensional versus two-
of useful examples. dimensional arrays
How to work with array
constants
Understanding Array Formulas
Techniques for working with
If you do any computer programming, you’ve probably been exposed to the con- array formulas
cept of an array. An array is simply a collection of items operated on collectively
or individually. In Excel, an array can be one dimensional or two dimensional. Examples of multicell array
These dimensions correspond to rows and columns. For example, a one- formulas
dimensional array can be stored in a range that consists of one row (a horizontal
array) or one column (a vertical array). A two-dimensional array can be stored in a Examples of array formulas that
rectangular range of cells. Excel doesn’t support three-dimensional arrays (but its occupy a single cell
VBA programming language does).
As you’ll see, arrays need not be stored in cells. You can also work with arrays
that exist only in Excel’s memory. You can then use an array formula to manipu-
late this information and return a result. An array formula can occupy multiple
cells or reside in a single cell.
This section presents two array formula examples: an array formula that occupies
multiple cells and another array formula that occupies only one cell.

313