Excel Linear Interpolation
Linear interpolation in excel means forecasting or guessing the upcoming next value of any certain variable given on the current data, here we create a straight line which connects two values and we estimate the future value through it, in excel we use forecast function and a lookup function to do a linear interpolation.
Interpolation is a mathematical or statistical tool that 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?
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 the Bangalore region for some date. The 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 interpolationInterpolationInterpolation is the mathematical procedure applied to derive value in between two points having a prescribed value. It approximates the value of a given function at a given set of discrete points. It can be applied in estimating varied concepts of cost, mathematics, statistics. 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 FORECASTExcel Like FORECASTThe FORECAST function in Excel is used to calculate or predict the future value based on existing values and the statistical value of the forecast. If we know the past data, we may use the function to forecast the future value., OFFSET, MATCH. Let us see in brief of these formulas before we go ahead.
FORECAST() – This Forecast excel functionForecast Excel FunctionThe FORECAST function in Excel is used to calculate or predict the future value based on existing values and the statistical value of the forecast. If we know the past data, we may use the function to forecast the future value. 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 functionMatch Excel FunctionThe MATCH function looks for a specific value and returns its relative position in a given range of cells. The output is the first position found for the given value. Being a lookup and reference function, it works for both an exact and approximate match. For example, if the range A11:A15 consists of the numbers 2, 9, 8, 14, 32, the formula “MATCH(8,A11:A15,0)” returns 3. This is because the number 8 is at the third position. 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. The 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 equalGreater Than Or EqualThe “greater than or equal to” is a comparison or logical operator that helps compare two data cells of the same data type. to the look_up value and should be sorted in descending order.
OFFSET() – This Offset functionOffset FunctionThe OFFSET function in excel returns the value of a cell or a range (of adjacent cells) which is a particular number of rows and columns from the reference point. 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 columnsRows And ColumnsA cell is the intersection of rows and columns. Rows and columns make the software that is called excel. The area of excel worksheet is divided into rows and columns and at any point in time, if we want to refer a particular location of this area, we need to refer a cell. 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 the 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.
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 the 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
- It 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.
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 –