Table of Contents
Linear interpolation is a mathematical method used to estimate values between two known data points. In Excel, this can be performed in a step-by-step manner by following these instructions:
1. Organize your data into two columns, with the known data points in the first column and the corresponding values in the second column.
2. Select the cell where you want to display the interpolated value.
3. In the formula bar, type “=LINEST(known_y’s, known_x’s, 1, TRUE)” and press Enter. This will perform a linear regression and return the slope and y-intercept values.
4. In the formula bar, type “=slope*known_x + intercept” and press Enter. This will calculate the estimated value based on the slope and y-intercept values.
5. Repeat this process for each data point to estimate the values in between the known data points.
By following these steps, you can easily perform a step-by-step linear interpolation in Excel to estimate values for any given data set.
Linear Interpolation in Excel: Step-by-Step Example
Interpolation is the process of estimating an unknown value of a function between two known values.
Given two known values (x1, y1) and (x2, y2), we can estimate the y-value for some point x by using the following formula:
y = y1 + (x-x1)(y2-y1)/(x2-x1)
This tutorial explains how to use linear interpolation to find some unknown y-value based on an x-value in Excel.
Example: Linear Interpolation in Excel
Suppose we have the following dataset in Excel:
If we create a quick plot of the data, here’s what it would look like:
Now suppose that we’d like to find the y-value associated with a new x-value of 13. We can see that we have measured y-values for x-values of 12 and 14, but not for an x-value of 13.
We can use the following formula to perform linear interpolation in Excel to find the estimated y-value:
=FORECAST(NewX,OFFSET(KnownY,MATCH(NewX,KnownX,1)-1,0,2), OFFSET(KnownX,MATCH(NewX,KnownX,1)-1,0,2))
Here’s how to use this function to estimate the y-values associated with an x-value of 13:
The estimated y-value turns out to be 33.5.
If we add the point (13, 33.5) to our plot, it appears to match the function quite well:
Note that in order for this function to work, the new x-value should fall within the range of the existing x-values.
You can find more Excel tutorials .