## Cubic Interpolation in a Two Way Table by Means of Worksheet Formulas

To perform cubic interpolation between data points in a two-way table, we use a procedure similar to the one for linear interpolation. Figure 5-18 shows the table of viscosities that was used earlier. In this example we want to obtain the viscosity of a 63% solution at 55°F. The shaded cells are the values that bracket the desired x and y values.

 A B C Ü E F G H ! 1 Viscosity of Heat Transfer Fluid (cps) 2 Volume Percent Ethylene Glycol 3 Temp, °F 20% 30% 40% 50% 60% 70% 80% 90% 4 G 13,76 19 34 30.08 45.58 65.04 107 77 5 10 6.83 10.13 14.26 22.06 33.31 46.89 71.87 6 20 3.90 5.38 7.74 10.86 16.50 24.79 34.48 49.94 7 30 3,14 4.33 6.09 8.48 12.68 18.77 25 84 35.91 6 40 259 3.54 4.91 6.77 9.90 14.45 19.71 26.59 9 50 2.18 2.95 4.04 5.50 7.85 11.31 16.29 20.18 10 60 1 86 2.49 3.38 4.55 6.33 897 12 05 15.65 11 70 1.61 2.13 2.87 3.81 5.17 7.22 9.62 12.37 12 80 1.41 1.34 2.46 3.23 4.26 5.as 7,79 9 93 13 90 1.24 1.60 2 13 2.76 3.58 4.B5 6.38 8.10 14™ 100 1.11 1.41 1.87 2.39 3.03 4.04 5.28 6 68 15 110 0.99 1.25 1,64 2,08 2,58 3,40 4.41 5.58

Figure 5-18. Cubic interpolation in a two-way table. The shaded cells are the ones used in the interpolation, (folder 'Chapter 05 Interpolation', workbook 'Interpolation II', module' Cubic Interpolation 2-Way')

Figure 5-18. Cubic interpolation in a two-way table. The shaded cells are the ones used in the interpolation, (folder 'Chapter 05 Interpolation', workbook 'Interpolation II', module' Cubic Interpolation 2-Way')

We'll use the InterpC function to perform the interpolation. Figure 5-19 shows the z values, interpolated at y = 63% using the four bracketing y values, for the four bracketing x values. The formula in cell M8 is

 L I M I 7 X zaty=63% 8 40 11.15 9 50 8.80 10" 60 7.D5 11 70 5.73

Figure 5-19. First steps in cubic interpolation in a two-way table, (folder 'Chapter 05 Interpolation', workbook 'Interpolation II1, module ' Cubic Interpolation 2-Way')

Figure 5-19. First steps in cubic interpolation in a two-way table, (folder 'Chapter 05 Interpolation', workbook 'Interpolation II1, module ' Cubic Interpolation 2-Way')

Then, in this one-way table, we use the formula =lnterpC(L15,\$L\$8:\$L\$11,\$M\$8:\$M\$11) in cell M15 to obtain the final interpolated result, as shown in Figure 5-20.

 L M 14 ; X z(tnterp) 15 j 55 7.86 |

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')

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-21. The syntax of the function is \nterpC2(x_lookup,y_lookup,known_x s,known_y s,known_z s)

The arguments xjookup and yjookup are the lookup values. The arguments known_x's and known_y's 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

iii. 4.4. a 1.1.« iii 4.4.4.4.4.4.4-4-4.4____ 111 1 1 1 11, I i 1 1 til 1 tTt.f i t 1 i I > j I

Function lnterpC2(x_lookup, yjookup, known_x's, known_y'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 known_y's must be in ascending order. ' In first call to Sub, XX is array of four known_y's ' and YY is array of corresponding Z values, pointer is yjookup. ' 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 YY is the array of interpolated Z values, pointer is xjookup.

Dim M As Integer, N As Integer Dim R As Integer, C As Integer

Dim XX(4) As Double, YY(4) As Double, ZZ(4) As Double, Zlnterp(4) As _ Double

R = Application.Match(xJookup, known_x's, 1) C = Application.Match(yJookup, known_y's, 1) If R < 2 Then R = 2

If R > known x's.Count - 2 Then R = known x's.Count - 2

If C > known_y's.Count - 2 Then C = known_y's.Count - 2 For N = 1 To 4

' Create array of four known_y 's, four known_z's, four known_x's ' Check values to see whether ascending or descending, 'and transfer input data to arrays in ascending order always. XX(N) = known_x's(R + N - 2) If known_y's(C + 2) > known_y's(C -1) Then For M = 1 To 4 YY(M) = known_y's(C + M - 2)

If known_z's(R + N - 2, C + M - 2) = "" Then lnterpC2 = _ CVErr(xlErrNA): Exit Function

ZZ(M) = known_z's(R + N - 2, C + M - 2) Next M Else

If known_z's(R + N - 2, C - M + 3) = "" Then lnterpC2 = _ CVErr(xlErrNA): Exit Function

ZZ(M) = known_z's(R + N - 2, C - M + 3) Next M End If

'This is array of interpolated Z values at yjookup

### Next N

lnterpC2 = CI(x_lookup, XX, Zlnterp) End Function i . , , » « . . . . . . . i . I . I « . i I i i » i • 4.4.4.^4.4.4.4.4.4.A4.A+X +++4.++4.4.4.4.4.+++ +++J

Private Function CI(lookup_value, known_x's, known_y's) ' Performs cubic interpolation, using an array of known_x's, known_y's (four values of each)

' This is a modified version of the function InterpC.

Dim i As Integer, j As Integer Dim Q As Double, Y As Double

If i <> j Then Q = Q * (lookup_value - known_x's(j)) / (known_x's(i) - _ known_x's(j)) Next j

End Function

Figure 5-21. Cubic interpolation function procedure for use with a two-way table, (folder 'Chapter 05 Interpolation', workbook 'Interpolation II', module 'Cubic2Way')

The function lnterpC2 was used to obtain the viscosity of a 74.5% weight percent solution of ethylene glycol at 195°F, as illustrated in Figure 5-22. The formula in cell M7 was

This custom function provides a convenient way to perform interpolation in a two-way table.

 K j L L m Using Cubiclnterp2Way function Temp Percent Viscosity Jj 195 74.5% 1.13

Figure 5-22. Result returned by the cubic interpolation function, (folder 'Chapter 05 Interpolation', workbook 'Interpolation II', sheet 'Cubic Interp 2-Way by Custom Fn')

Figure 5-22. Result returned by the cubic interpolation function, (folder 'Chapter 05 Interpolation', workbook 'Interpolation II', sheet 'Cubic Interp 2-Way by Custom Fn')