Page 116 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 5 INTERPOLATION 93

Figure 5-20. Final step in cubic interpolation in a two-way table.
(folder 'Chapter 05 Interpolation', workbook 'Interpolation II', module ' Cubic Interpolation 2-Way')

Cubic Interpolation in a Two-way Table
by Means of a Custom Function

The cubic interpolation macro was adapted to perform cubic interpolation in
a two-way table. The calculation steps were similar to those described in the
preceding section. The cubic interpolation function shown in Figure 5-13 was
converted into a subroutine CI; the main program is similar to the Lagrange
fourth-order interpolation program of Figure 5- 12.
The VBA code is shown in Figure 5-2 1. The syntax of the function is
I n terpC2(x-/ookup,y-/ookup, kno wn-x 's,kno wnj 's,kno wn-z 's)
The arguments x-lookup and y-lookup are the lookup values. The arguments
known-x's and knownq/& are the one-dimensional ranges of the x and y
independent variables (in Figure 5-20, the column of temperature values and the
row of volume percent values). The argument known-z's is the table of
dependent variables (the two-dimensional body of the table).

Option Explicit
Option Base 1
'++++++++++++++++++++++++++++++++++++++++~i++ii+iiiiiii++++++i
Function InterpC2(x-lookup, y-lookup, known-x's, knownj's, - known-z's)
' known-x's are in a column, known-y's are in a row, or vice versa.
' In this version, known-x's and knownj's must be in ascending order.
' In first call to Sub, XX is array of four known-y's
' and W is array of corresponding Z values, pointer is y-lookup.
' This call is made 4 times in a loop,
' obtaining 4 interpolated Z values, ZZ
' In second call to Sub, XX is array of four known-x's
' and W is the array of interpolated Z values, pointer is x-lookup.

Dim M As Integer, N As Integer
Dim R As Integer, C As Integer
Dim XX(4) As Double, W(4) As Double, ZZ(4) As Double, Zlnterp(4) As -
Double
R = Application.Match(x-lookup, known-x's, 1)
C = Application.Match(y-lookup, knownj's, I)
If R < 2 Then R = 2
If R > known-x.s.Count - 2 Then R = known-x-s.Count - 2