In this article, we will learn to use the Excel function “Trend” for linear interpolation and verify it through the manual calculation
The 1st question that comes to mind is what is linear interpolation?
Linear interpolation is the method of estimating values between two sets of known values which are linearly connected.
Data
Take the following data and calculate Y corresponds to X = 5:
| Known X’s | Known Y’s |
| 2.00 | 5.00 |
| 4.00 | 8.00 |
| 6.00 | 11.00 |
| 8.00 | 14.00 |
Required
To calculate the value of Y corresponds to X = 5
Condition
Points are linearly connected. we know that it means if we draw the lines then lines has constant slope
Linear Interpolation through Trend Function of Microsoft Excel
- Select the output cell, then click on Insert function “fx”
- In the search bar type “Trend” and click on Go
- Select “Trend” and press OK
- Below Window appears on the screen
- In the Known Y’s range select your Y range values, In the X’s range select the corresponding X range values. Here New X’s is the value, for which the corresponding Y is required. “Const” is an optional argument it will be “True” or an empty cell, if there is Y-intercept, it will be false if we know the constant term is 0.
- Apply formula for the given data as shown in the below picture:
- Press OK, in the output cell you get value of Y=9.5 corresponding to the X=5
Linear Interpolation through Manual Calculation
Here we calculate “Y” at the corresponding new value X = 5 and verify the output result of the Excel function “Trend”
First, select the two points X1 & X2 and take corresponding Y’s values (i.e. Y1 & Y2)
Take;
X1=4.0 and corresponding Y1=8.0
X2=6.0 and corresponding Y2=11.0
Since points are linearly connected means having a constant slope (m1=m2), therefore,
Since the manually calculated value is the same as the value calculated from the “Trend function” of Excel, therefore, result is verified.
Conclusion
- Using the “Trend” functions in Microsoft Excel, we interpolate the data accurately in a minimum time.
- We can interpolate thousands of values for future data from present data by using the “Trend” function of Microsoft Excel
Attachment
I am attaching an Excel sheet of linear interpolation, which makes your calculation easier and faster;