# 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).

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.