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.

Was this article helpful?

## Post a comment