Page 112 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 5 INTERPOLATION 89

Function InterpC(1ookup-value, known-x's, known-y's)
' Performs cubic interpolation, using an array of known-x's, known-y's.
' The known-x's must be in ascending order.
' Based on XLM code from Excel for Chemists", page 239,
' which was based on W. J. Orvis' code.

Dim row As Integer
Dim i As Integer, j As Integer
Dim Q As Double, Y As Double
row = Application.Match(lookup-value, known-x's, 1)
If row c 2 Then row = 2
If row > known-x's.Count - 2 Then row = known-fs.Count - 2
For i = row - 1 To row + 2
Q=l
Forj = row- 1 To row +2
If i <> j Then Q = Q * (lookup-value - known-x's(j)) / (known-x's(i) - -
known-x's(j))
Next j
Y = Y + Q * known-y's(i)
Next i
InterpC = Y
End Function
Figure 5-13. Cubic interpolation function procedure.
(folder 'Chapter 05 Interpolation', workbook 'Interpolation 1', module 'Cubichterpolation':

Figure 5-14 illustrates the use of the custom function to interpolate values in
the table shown in Figure 5-12; cell H22 contains the formula
=I nterpC( G22, $A$3: $A$47, $B$3: $B$47)

Figure 5-14. Using the InterpC function procedure for cubic interpolation.
(folder 'Chapter 05 Interpolation', workbook 'Interpolation I', sheet 'Linear Interpolation')

Cubic Interpolation in a Table
by Using the TREND Worksheet Function
In the TREND function, the array known-x's can include one or more sets of
independent variables. For example, suppose column A contains x values. You
can enter x2 values in column B and x3 in column C and then regress columns A
through C against the y values in column D to obtain a cubic interpolation