Linear Interpolation - Knowledge Sharing Platform

In this article, we will learn to use the Excel function “Trend” for linear interpolation and verify it through the manual calculation

The 1^st 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.

Table of Contents

Data

Take the following data and calculate Y corresponds to X = 5:

Table 1:Available Data

To calculate the value of Y corresponds to X = 5

Points are linearly connected. we know that it means if we draw the lines then lines has constant slope

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.

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. Y₁ & Y₂)

Take;

X₁=4.0 and corresponding Y₁=8.0

X₂=6.0 and corresponding Y₂=11.0

Since points are linearly connected means having a constant slope (m₁=m₂), therefore,

Since the manually calculated value is the same as the value calculated from the “Trend function” of Excel, therefore, result is verified.

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

I am attaching an Excel sheet of linear interpolation, which makes your calculation easier and faster;