Linear Interpolation in a Table by Means of Worksheet Formulas

To find the value of y at a point x that is intermediate between the table values xq, yo and Xj, use the equation for simple linear interpolation (equation 5-1).

Interpolation Formula Excel

Figure 5-6. Freezing point of ethylene glycol solutions (data from Figure 5-1). (folder 'Chapter 05 Interpolation', workbook 'Interpolation I', sheet 'Linear Interpolation')

Wt%Ethylene Glycol

Figure 5-6. Freezing point of ethylene glycol solutions (data from Figure 5-1). (folder 'Chapter 05 Interpolation', workbook 'Interpolation I', sheet 'Linear Interpolation')

In the following example, we'll assume that values of the independent variable x in the table are in ascending order, as in Figure 5-1, where the independent variable is wt% ethylene glycol. We want to find the freezing point for certain wt% values. Figure 5-2 shows the data (see folder 'Chapter 05 Interpolation', workbook 'Interpolation I', sheet 'Linear Interpolation'); it's clear that, since most of the points are close together, we can use linear interpolation without introducing too much error.

You can create a linear interpolation formula using Excel's MATCH and INDEX functions. If match_type_num = 1, MATCH returns the position of the largest array value that is less than or equal to lookup_value. The array must be in ascending order. Use this value in the INDEX function to return the values of jc0, y0, and^i, as shown in the following:

The preceding formulas were applied to the data shown in Figure 5-1 to find the freezing point of a 33.3 wt% solution of ethylene glycol. The following named ranges were used in the calculations: known_x's ($A$3:$A$47), known_y's ($B$3:$B$47), lookup_value ($F$6), position ($G$6). The intermediate calculations and the final interpolated value are shown in Figure 5-7.

F

G

H

L „J

3

Stepwise calculations to develop formula Look upVatue Value

4

5

Formula used In column 0

6

33.3

18

(position)

=MATCH(LookupValue,XValuss,t)

7

33

(x_0)

=INDEX{XValues,G6)

8

34

OU)

=INDEX(XValues,G6+1)

9

2.9

(y_0)

=INDEX(YValues,G6)

10

1.4

(yj)

=INDEX(YValues,G6+1)

11

2.45

=G9+(F6-G7)*(G1 0-G9)/(G8-G7)

Figure 5-7. Linear interpolation: intermediate calculations, (folder 'Chapter 05 Interpolation', workbook 'Interpolation I', sheet 'Linear Interpolation')

Figure 5-7. Linear interpolation: intermediate calculations, (folder 'Chapter 05 Interpolation', workbook 'Interpolation I', sheet 'Linear Interpolation')

position =MATCH(lookup_value,known_x's,1)

=INDEX(known_x's, position) =INDEX(known_x's,position+1) =INDEX(known_y's,position) =INDEX(known_y's,position+1)

The formulas in cells G6:G11 can be combined into a single "megaformula" for linear interpolation, shown below and used in cell G15.

=INDEX(Walues,MATCH(LookupValue,XValues,1))+(F15-INDEX(XValues, MATCH(LookupVaiue,XValues,1)))*(INDEX(YValues,MATCH(LookupValue, XValues,1)+1)-INDEX(Walues,MATCH(LookupValue,XValues,1)))/ (INDEX(XValues,MATCH (LookupValue.XValues, 1 )+1 )-INDEX(XValues, MATCH(LookupValue,XValues, 1)))

F

G

13

Megaformula

version

14

LookupValue

Value

15

33.3

2.45

Figure 5-8. Linear interpolation: final interpolated value.

(fofder 'Chapter 05 Interpolation', workbook 'Interpolation I', sheet 'Linear Interpolation')

Figure 5-8. Linear interpolation: final interpolated value.

(fofder 'Chapter 05 Interpolation', workbook 'Interpolation I', sheet 'Linear Interpolation')

If you use the megaformula, the formulas in cells G6:G11 are no longer required.

0 0

Post a comment