Microsoft Office Tutorials and References

In Depth Information

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 change (that is, the least improvement). This formula 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 reference

with an array constant. This is useful in situations in which the values in the referenced range do not change.

A notable exception to using an array constant in place of a range reference in a func-

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

Figure 14-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)