Microsoft Office Tutorials and References

In Depth Information

**Chapter 3: Saying “Array!” for Formulas and Functions**

formulas and functions. That is, the association of some values as one

cohesive group exists just for the purpose of calculating results. An array differs

from the named areas (a range of cells) that you can create in Excel. Named

areas become part of the worksheet and can be referenced at any time.

Named areas are set using the New Name dialog box, shown in Figure 3-1. In

contrast, there is no such dialog box or method to create arrays that can be

referenced from functions or formulas. Arrays, instead, are embedded in formulas.

Figure 3-1:

Creating a

named area

with the

New Name

dialog box.

Named areas are easily referenced in formulas. For example, if a workbook

contains a named area Sales, then the values of all the cells in Sales can be

summed up like this:

=SUM(Sales)

Assume that Sales contains three cells with these values — 10, 15, and 20.

These values of course can be directly entered in the SUM function like this:

=SUM(10,15,20)

This is almost an array, but not quite. Excel recognizes a group of values

to be an array when they are enclosed with braces ({ and }). Therefore, to

enter the array of values into the function, the entry looks like this:

=SUM({10,15,20})

Essentially the braces tell Excel to treat the group of values as an array. So

far, you may be wondering about the usefulness of an array, but in the next

section, I show you how using arrays with standard functions such as SUM

can provide sophisticated results.

To enter values as an array within a function, enclose them with braces.

Braces have a curly look to them, not to be confused with brackets. On a

typical keyboard, braces and brackets are on the same key. Holding the Shift key

while pressing the brace/bracket key provides the brace.