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

0 0

Post a comment