Microsoft Office Tutorials and References
In Depth Information
In such a case, you probably want to interpolate. In other words, because the lookup value (3) is halfway
between existing x values (2 and 4), you want the formula to return a y value of 21.00 — a value halfway
between the corresponding y values 18.00 and 24.00.
Formulas to perform a linear interpolation
Figure 8-17 shows a worksheet with formulas in column B. The value to be looked up is entered into cell B1.
The final formula, in cell B16, returns the result. If the value in B3 is found in the x range, the corresponding y
value is returned. If the value in B3 is not found, the formula in B16 returns an estimated y value, obtained us-
ing linear interpolation.
Figure 8-17: Column B contains formulas that perform a lookup using linear interpolation.
It's critical that the values in the x range appear in ascending order. If B1 contains a value less than the lowest
value in x or greater than the largest value in x , the formula returns an error value. Table 8-2 lists and describes