Microsoft Office Tutorials and References
In Depth Information
Chapter 3: Saying “Array!” for Formulas and Functions
Saying “Array!” for Formulas
In This Chapter
▶ Understanding arrays
▶ Creating formulas that use arrays
▶ Using functions that return arrays of data
Excel is really quite sophisticated — its many built-in functions make your
work easier. On top of that, Excel allows you to tell functions to work on
entire sets of values, called arrays, which makes for even more clever analysis.
An array is a set of two or more values (for example, the contents of two
or more worksheet cells, or even the contents of two or more worksheet
ranges). Certain functions use arrays for arguments.
You may be thinking “Hey, how is this different from just entering a bunch
of arguments?” You’re right in the comparison. For example, the SUM
function can take up to 255 arguments. Isn’t this the same as giving the function
an array with 255 values? Well, yes and no. It’s the same idea, but using the
array approach can streamline your work, as you soon see.
There is even another side to array functions. Some of the functions return an
array. Yes, that’s right. Most of the time a function returns a single value into
a single cell. In this chapter, I show you how a function returns a group of
values into multiple cells.
An array is like a box. It can hold a number of items. In Excel, an array holds
a collection of values or cell references. These arrays are used exclusively in