Linear Interpolation in Excel

Excel Linear Interpolation (Table of Contents)

• What is Interpolation?
• Interpolation With Forecast Function in Excel
• Examples of Linear Interpolation in  Excel

What is Interpolation?

In the world of mathematics, interpolation is a method of creating new data points with the help of known data points. Interpolation estimates the value based on the existing available data values which are relatable.

For Example – If we drive a bike at 60 Km/hour and reached a particular destination in 1 hour and if we drive at 45 Km/hour and reached a particular destination in 45 minutes. So how much time will it take to reach a destination if we drive at 30 Km/hour.

Using simple mathematics, we can calculate the missing values in the above example. We need to insert the below formula in cell B4.

=B2+ (A4-A2)*(B3-B2)/ (A3-A2)

So on the basis of the above formula, we can say it takes 30 minutes to reach the destination if we drive at 30 km/hour.

Let’s try to break the above formula and understand the formula in detail.

=B2+ (A4-A2)*(B3-B2)/(A3-A2)

In the above example, the last section of the formula which is highlighted in red calculates how much time taken changes whenever the speed of bike changes by 1. In our example, time taken changes by 1 minute when the speed of bike changes by 1 km/hour.

=B2+ (A4-A2)*(B3-B2)/ (A3-A2)

The second section (in blue above) calculates how far our speed of bike is away from the first speed of bike given, then multiplies it by the value calculated above.  Based on our example, it is 30 (Cell A4) minus 60 (Cell A2), the result of which is then multiplied by 1 (which equals -30).

=B2+ (A4-A2)*(B3-B2)/ (A3-A2)

Finally, the first section of the formula (in brown above); we add the first value of the speed of the bike.  In our example, this provides the final result of 60 + (-30)*(1) = 30 Minutes.  In school, we used to use the below formula to calculate the missing value of Y.

Y = Y1 + (X-X1)* (Y2-Y1)/(X2 – X1)

This is an example of how to calculate the missing values with the help of a manual formula to understand interpolation.

Excel has an inbuilt function that does a similar calculation as above and it is known as FORECAST Function. Now we will learn this function in detail now.

Interpolation with Forecast Function in Excel

Forecast is a Worksheet Function available in MS Excel and it uses linear regression to find out the missing value. Forecast as its name suggests it is used to forecast the future value of a data point, but it can also be used to interpolate a value. Basically, it is used to calculate the future value based on the Existing values of a specific data set.

Syntax of Forecast Function

Arguments of Forecast Function:

•  x – It is a data point for which we want to know the resultant value.
• Known_y’s – Range of cells containing the values of Y.
• Known_x’s – Range of cells containing the values of X.

Now considering the same above example, let us try to use the Forecast function.

In our example,

x – Cell B4 (as we want to find out the time taken when speed in 30 km/hour).

Known_y’s – Cell B2 to Cell B3 (Time taken for the known speed of bike).

Known_x’s – Cell A2 to Cell A3 (Speed of the bike already given for which we know the time taken).

So final formula in cell B4 will be as below:

=FORECAST(A4,B2:B3,A2:A3)

And as you can see the final result is the same i.e. 30 Minutes.

For creating a chart, go to Insert menu click on Scatter and then select Scatter With Smooth Lines and Markers.

If we look at the below chart of the above example, we can say the data set has a linear relationship and known as linear interpolation.

Examples of Linear Interpolation in  Excel

Let’s understand the Linear Interpolation in Excel with some examples.

Example #1

Suppose you have sales and profit data for previous years & you want to know the profit of the current year if you achieve a certain level of sales.

Look at the below table. You have sales data from 2016 to 2018 and you want to know what should be the profit if your sales is Rs. 40,00,000 in 2019.

So with the help of Forecast function, we can interpolate the profit of 2019 when sales is Rs. 4,000,000

The formula in cell C5 will be as below:

=FORECAST (B5, C2:C4, B2:B4)

After using Forecast formula the answer shown below.

The result for the Forecast function will be Rs. 875,000 based on the sales & profit data available from 2016 to 2018.

In this example Forecast function interpolate the value based on all data available and not just starting and Endpoint. As you can see in the charts, profit is moving is exactly the same as sales. Even if we calculate the value manually, it will still give us the same result.

Example #2

We have data of the last 9 matches of the cricket team which has wickets gone and runs scored by the team.

We want to find out how many runs will team scores if 8 wickets fall.

So we will again use the same Forecast Function as above.

After applying the formula, we get the result of 302 Runs if we 8 wickets falls on the basis of a linear regression of last 9 matches played by the team.

In this example, data was not linear and you can see that in the below graph. But still, Forecast function helped us to interpolate the runs with the help of data of previous matches.

Things to Remember About Linear Interpolation in Excel

• Forecast function is used to forecast/estimate the value based on the existing available values but it can also help to interpolate the missing value.
• In case data is not linear, Forecast function will not give you the accurate value which is based on linear interpolation but it will give you the nearest value.
• If x in the Forecast function is text or non-numeric, Forecast function returns the #VALUE error.
• If Known_x’s and Known_y’s does not contain any data or contains a different set of data points, Forecast Function will give a #N/A error.
• If the variance of Known_x’s is zero then the Forecast function gives the #DIV/O error.

Recommended Articles

This is a guide to Linear Interpolation in Excel. Here we discuss How to Use Linear Interpolation in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. Linear Regression in Excel
2. Linear Programming in Excel
3. Statistics in Excel
4. Interpolate in Excel