Microsoft Office Tutorials and References

In Depth Information

To understand how this formula works, you need to understand the INDIRECT function. This function's first ar-

gument is a text string in the form of a cell reference (or a reference to a cell that contains a text string). In this

example, the text string is created by the ADDRESS function, which accepts a row and column reference and

returns a cell address.

Looking up a value using linear interpolation

Interpolation
refers to the process of estimating a missing value by using existing values. For an illustration of

this concept, see Figure 8-16. Column D contains a list of values (named
x
), and column E contains correspond-

ing values (named
y
).

Figure 8-16:
This worksheet demonstrates a table lookup using linear interpolation.

The worksheet also contains a chart that depicts the relationship between the
x
range and the
y
range graphic-

ally, and also includes a linear trendline. As you can see, an approximate linear relationship exists between the

corresponding values in the
x
and
y
ranges: As
x
increases, so does
y
. Notice that the values in the
x
range are

not strictly consecutive. For example, the
x
range doesn't contain the following values: 3, 6, 7, 14, 17, 18, and

19.

You can create a lookup formula that looks up a value in the
x
range and returns the corresponding value from

the
y
range. But what if you want to estimate the
y
value for a missing
x
value? A normal lookup formula does

not return a very good result because it simply returns an existing
y
value (not an estimated
y
value). For ex-

ample, the following formula looks up the value 3 and returns 18.00 (the value that corresponds to 2 in the
x

range):