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.
 
Search JabSto ::




Custom Search