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
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