Page 374 - Excel 2007 Bible

22_044039 ch17.qxp 11/21/06 11:08 AM Page 331
Introducing Array Formulas
You can use additional array formulas to calculate other measures for the data in this example. For example,
the following array formula returns the largest change (that is, the greatest improvement). This formula
returns 23, which represents Linda’s test scores.
{=MAX(C2:C15-B2:B15)}
FIGURE 17.17
Without an array formula, calculating the average change requires intermediate formulas in column D.

The following array formula returns the smallest change (that is, the least improvement). This formula 17
returns –11, which represents Nancy’s test scores.
{=MIN(C2:C15-B2:B15)}

Using an array in lieu of a range reference
If your formula uses a function that requires a range reference, you may be able to replace that range refer-
ence with an array constant. This is useful in situations in which the values in the referenced range do not
change.
NOTE A notable exception to using an array constant in place of a range reference in a function is
NOTE
with the database functions that use a reference to a criteria range (for example, DSUM).
Unfortunately, using an array constant instead of a reference to a criteria range does not work.
CROSS-REF For information about lookup formulas, refer to Chapter 15.
CROSS-REF

Figure 17.18 shows a worksheet that uses a lookup table to display a word that corresponds to an integer.
For example, looking up a value of 9 returns Nine from the lookup table in D1:E10. The formula in cell C1 is
=VLOOKUP(B1,D1:E10,2,FALSE)

331