Page 110 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 5 INTERPOLATION 87

The argument lookup-value is the value of the independent variable for
which you want the interpolated y value; known-x's and known-y's are the
arrays of independent and dependent variables, respectively, that comprise the
table. The table must be sorted in ascending order of known-XIS. Figure 5-11
illustrates the use of the custom function to interpolate values in the table shown
in Figure 5- 1 ; cell G24 contains the formula

=InterpL(F22,$A$3:$A$54,$B$3:$B$54)

Figure 5-11. Using the InterpL function for linear interpolation.
(folder 'Chapter 05 Interpolation', workbook 'Interpolation 1', sheet 'Linear Interpolation')

The custom function can be applied to tables in either vertical or horizontal
format.

Cubic Interpolation
Often, values in a table change in such a way that linear interpolation is not
suitable. Cubic interpolation uses the values of four adjacent table entries (e.g.,
at xo, XI, x2 and x3) to obtain the coefficients of the cubic equation y = a + bx + cx2
+ dx3 to use as an interpolating function between XI and x2. For example, to find
the freezing point for a 33.3 wt% solution of ethylene glycol using cubic
interpolation requires the four table values in Figure 5-12 whose x values are
highlighted.
A convenient way to perform cubic interpolation is by means of the
Lagrange fourth-order polynomial

(x-x2 >(x-x3 -x4 (x x3 >(x -x4 )
Yx = Yl + Y2
- '2 )('1 -x3 -x4 (x2 )(x2 -x3 - x4
+ (x - >(x - x2 - x4) (x - >(x - x2 - x3) Y4 (5-2)
(x3 - x1 )(x3 - x2 )(x3 - x4 1 y3 (x4 - )(x4 - x2 >(x4 - x3