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.
Linear Interpolation Background
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:
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.
MATCH and INDEX Functions
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.
Linear Interpolation in Excel
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 gave cell C2 the name “x” in this example by creating a named cell. The formula returns 40, which is the greatest temperature that is less than 53, our x-value.
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.
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.
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.
Wrap-Up
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.
