Linear Program Polynomial Interpolation Method
So far in posts on looking up tabular data in Excel, I’ve focused on how to extract known x- and y-values from a table. But what if the data in the table is too “coarse” and you need better accuracy? Well, it’s also possible to perform linear interpolation in Excel, which enables you to estimate a y-value for any x-value that is not provided explicitly in the data.
In order to perform a linear interpolation in Excel, we’ll use the equation below, where x is the independent variable and y is the value we want to look up:
[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]
- The Lagrange form of the interpolating polynomial is a linear combination of the given values. In many scenarios, an efficient and convenient polynomial interpolation is a linear combination of the given values, using previously known coefficients.
- This method fits a different linear polynomial between each pair of data points for curves, or between sets of three points for surfaces. Nearest neighbor. Nearest neighbor interpolation. This method sets the value of an interpolated point to the value of the nearest data point. Therefore, this method does not generate any new data points.
Chapter 3 Interpolation Interpolation is the process of defining a function that takes on specified values at specified points. This chapter concentrates on two closely related interpolants: the piecewise cubic spline and the shape-preserving piecewise cubic named “pchip.” 3.1 The Interpolating Polynomial.
This method assumes that the change in y for a given change in x is linear.
In most cases this will provide results that are sufficiently accurate. However, if you need even greater accuracy, you may want to consider a more advanced method such as cubic splines.
As shown in the equation above, we’ll need to find the values of x1, y1, x2, and y2. To find these, we’ll use the MATCH and INDEX functions.
I wrote about the syntax for these functions here, so if you need a refresher be sure to check that out. But basically, MATCH returns the location of a value (n) in a column or row of data. INDEX returns the actual value in the nth position of a row or column of data.
By using these functions together, we can extract the values of x1, y1, x2, and y2 we need for the interpolation.
Let’s take a look at how to perform this analysis on some real data.
The table below lists air density as a function of temperature in 20 degree Celsius increments. If we want to get data at any temperatures other than those in the first column, we’ll have to interpolate.
If we want to estimate the density at 53 degrees Celsius, we need Excel to find the values x1 = 40, y1 = 1.127, x2 = 60, and y2 = 1.067 in the table. Then we can use these values in the equation above.
Find the value of x1 with the following formula:
I’ve given cell C2 the name “x” in this example. This formula returns 40, which is the greatest temperature that is less than 53, our x-value.
[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]
Next, we can get y1 with the following formula:
This equation is very similar to that used for x1. I used the MATCH function to return the position of the greatest x-value that is less than “x” just as before. But rather than looking for the value in that position of the temperature data, I returned a value from the column of density data instead.
Polynomial Interpolation Matlab
Now, to get x2 and y2, we will use basically the exact same formulas with a slight difference. We’ll add 1 to the value returned by MATCH to get 60 for x1 and 1.067 for y.
Now, it’s just a simple matter of entering the formula for linear interpolation into the appropriate cell. I’ve used Named Ranges here again to make the formula clearer.
Polynomial Interpolation Calculator
The formula returns 1.088. As a quick check to see if this makes any sense, we can plot it on a curve of the known data:
Looks good!
The great thing about setting the formulas up in this way is that you can interpolate correctly between ANY pair of tabulated x- and y- values.
Polynomial Interpolation Pdf
So there you have it, a method to perform linear interpolation in Excel. Of course, this isn’t the only method, but I think it’s probably the most straightforward one. Do you have a preferred method? Let me know in the comments below.
[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]