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

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

Was this article helpful?

## Post a comment