Excel Formulas and Functions For Dummies, 3rd Edition

Chapter 3

Saying “Array!” for Formulas and Functions

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 ...