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:
The following array formula returns the smallest change (that is, the least improvement). This formula returns
–11 , which represents Nancy's test scores:
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