Microsoft Office Tutorials and References
In Depth Information
Table 8-2: Formulas for a Lookup Using Linear Interpolation
Cell
Formula
Description
Performs a standard lookup on the x range and returns the
looked-up value.
B3
=LOOKUP(B1,x,x)
Returns TRUE if the looked-up value equals the value to be
looked up.
B4
=B1=B3
Returns the row number of the x range that contains the
matching value.
B6
=MATCH(B3,x,0)
Returns the same row as the formula in B6 if an exact match
is found. Otherwise, it adds 1 to the result in B6.
B7
=IF(B4,B6,B6+1)
B9
=INDEX(x,B6)
Returns the x value that corresponds to the row in B6.
B10 =INDEX(x,B7)
Returns the x value that corresponds to the row in B7.
B12 =LOOKUP(B9,x,y)
Returns the y value that corresponds to the x value in B9.
B13 =LOOKUP(B10,x,y)
Returns the y value that corresponds to the x value in B10.
=IF(B4,0,(B1-B3)/
(B10-B9))
Calculates an adjustment factor based on the difference
between the x values.
B15
Calculates the estimated y value using the adjustment factor
in B15.
B16 =B12+((B13-B12)*B15)
Combining the lookup and trend functions
Another slightly different approach, which you may find preferable to performing lookup using linear interpola-
tion, uses the LOOKUP and TREND functions. One advantage is that it requires only one formula (see Figure
8-18).
Search JabSto ::




Custom Search