Excel Linear Interpolation
Linear interpolation in Excel is used to predict the values by rendering a straight line between the existing two data points on a graph.
Interpolation is a mathematical or statistical tool which is used to predict the values between 2 points on a curve or line. This tool is not only used in statistics but also used in many other fields like business, science, etc. wherever there is an opportunity to predict values between two data points.
How to do Linear Interpolation in Excel?
Let us see how can we do interpolation and linear interpolation in excel by looking at below examples.
Example #1 – Performing Interpolation to know the temperature of weather during different time zones
First, take down the temperature figures of Bangalore region for each hour and data would be as follows: –
The data shows that we have got the temperature details of Bangalore region for some date. On time column we have the time zones for the entire day and hour column we mentioned the hours’ number from the starting of the day like 12:00 AM would be 0 hours, 1:00 AM would be 1 hour and so on.
Now we are going to perform interpolation for the data in order to pull out the temperature value for the required time zone which may be any time not only the exact hour.
In order to perform interpolation, we have to use a few formulas in Excel like FORECAST, OFFSET, MATCH. Let us see in brief of these formulas before we go ahead.
FORECAST() – This Forecast excel function calculates or predicts the future value based on existing values along with a linear trend.
- X – This is the value for which we want to predict.
- Known_ys – This is the dependent values from the data and a mandatory field to be filled
- Known_xs – This is the independent values from the data and a mandatory field to be filled.
MATCH() – This Match excel function will return the relative position of a lookup value in a row, column or table that matches the specified value in a specified order.
- Lookup_value – This is the value that needs to be matched from the lookup_array
- Lookup_array – This is the range for searching
[match_type] – This can be 1,0,-1. Default would be 1. For 1 – Match will find the largest value which is less than or equal to look_up value and value should be in ascending order. For 0 – Match finds the first value exactly equal to lookup_value and no need to be sorted. For -1 – Match will find the smallest value which is greater than or equal to the look_up value and should be sorted in descending order.
OFFSET() – This Offset function will return a cell or range of cells which are specified number of rows and columns. The cell or range of cells will depend on the height and width in rows and columns we specify.
- Reference – This is the starting point from where the count of rows and columns will be done.
- Rows – No.of rows to offset below the starting reference cell.
- Columns – No.of columns to offset right from the starting reference cell.
- [height] – The height in rows from the returned reference. This is optional.
- [width] – The width in columns from the returned reference. This is optional.
As we have seen the formulas in brief that we are going to use to perform the interpolation. Now let us perform the interpolation as follows:
Type the formula in a cell that we need to see the temperature for different time zone. This tells that we have to select the cell that needs to be forecasted and offset & match function is used to select the known_ys and known_xs.
FORECAST($F$5 – Select the cell that has the time zone to be forecasted.
OFFSET($C$3:$C$26,MATCH($F$5,$B$3:$B$26,1)-1,0,2) – This is used to select the known_ys as a reference is taken temp column because these are the dependent values. The match function is used to generate the position of the value that we need to forecast and calculate the number of rows. Columns should be 0 because we want the dependent value on the same column which selected and height is 2 as we need to perform the forecast based on last 2 values.
OFFSET($B$3:$B$26,MATCH($F$5,$B$3:$B$26,1)-1,0,2) – This is used to select the known_xs as a reference is taken hour column because these are independent values and rest is the same as we had done for row count.
Now give some time zone in the cell that we had considered to forecast. Here the value entered is 19.5 which is 7:30 PM and we will get the temperature of 30 which is forecasted from the temperature values that are given an hourly basis.
Similarly, we can see temp figures for different time zone from this formula.
Example #2 – Performing Linear Interpolation to know the sales of an organization in 2018
Let’s assume that we got the sales details for an organization in 2018 as below. We have data in terms of days and their sales in cumulative. We got sales of 7844 units in first 15 days of the year, 16094 units in 50 days of the year and so on.
We can use the same formula that we used in interpolation to forecast sales value for different days which was not mentioned in the data that we consider. Here the sales are in a straight line(linear) as we had taken cumulatively.
If we want to see the number of sales that we had achieved in 215 days then we can get the forecasted number of sales for 215 days as below by considering the given sales data.
Similarly, we can find out the number of sales in that year by forecasting between the points that are given.
Things to Remember About “Linear Interpolation” in Excel
- Linear Interpolation in Excel is the least accurate method but it is fast and accurate if the table values are closely spaced.
- This also can be used in estimating values for a geographical data point, rainfall, noise levels, etc.
- It is very easy to use and not very accurate for non-linear functions.
- Apart from Excel Linear interpolation we also have different types of methods like Polynomial Interpolation, Spline Interpolation, etc.
You can download this Linear Interpolation Excel Template here – Linear Interpolation Excel Template
This has been a guide to Linear Interpolation in Excel. Here we will see how to perform interpolation and linear interpolation in excel along with some examples and downloadable excel template. You may learn more about excel from the following articles –