Chapter 18: Understanding and Using Array Formulas
18
You can use additional array formulas to calculate other measures for the data in this
example. For instance, 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)}The following array formula returns the smallest value in the Change column. This formula
returns –11, which represents Nancy’s test scores:{=MIN(C2:C15-B2:B15)}Using an array instead of a range reference
If your formula uses a function that requires a range reference, you may be able to replace
that range reference with an array constant. This is useful in situations in which the val-
ues in the referenced range do not change.A notable exception to using an array constant in place of a range reference in a function is with the database func-
tions 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.
Figure 18.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 as follows:=VLOOKUP(B1,D1:E10,2,FALSE)FIGURE 18.18
You can replace the lookup table in D1:E10 with an array constant.