Excel Linear Interpolation

Interpolation is a mathematical or statistical tool that predicts the values between two points on a curve or line. This tool is used in statistics and used in many other fields like business, science, etc., wherever there is an opportunity to predict values between two data points.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Linear Interpolation in Excel (wallstreetmojo.com)

How to do Linear Interpolation in Excel?

Example #1

Performing Interpolation to know the temperature of weather during different time zones

First, take down the temperature figures of the Bangalore region for each hour, and the data would be as follows: –

The data shows that we have got the temperature details of the Bangalore region for some date. We have the time zones for the entire day in the on-time column. In the hour column, we mention the hours from the start of the day, like 12:00 AM would be 0 hours, 1:00 AM would be 1 hour, and so on.

We will 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.read more for the data to pull out the temperature value for the required time zone, which may be any time, not only the exact hour.

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.read more, OFFSET, and MATCH. Let us see in brief 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.read more calculates or predicts the future value based on existing values and a linear trend.

  • X – This is the value for which we want to predict.Known_ys – This is the dependent value from the data and a mandatory field to be filled.Known_xs – This is the independent value from the data and a required field to be served.

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. read more 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_arrayLookup_array – This is the range for searching

[match_type] – This can be 1,0,-1. The default would be 1. For the 1 – MATCH function, you will find the largest value less than or equal to the look_up value. The value should be in ascending order. For 0 – MATCH function finds the first value exactly equal to lookup_value and no need to be sorted. For -1 – Match will find the smallest value 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.read more  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. read more 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 of rows and columns we specify.

  • Reference – This is the starting point from where it will count 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.read more will be done.Rows – Number of rows to offset below the starting reference cell.Columns – Number of columns to offset right from the starting reference cell.[height] – The height in rows from the returned reference. It is optional.[width] – The width in columns from the returned reference. It is optional.

We have seen the formulas in brief that we will 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. It tells that we have to select the cell that needs to be forecasted, and the OFFSET and MATCH function is used to determine 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 selected and height to be 2 as we need to perform the forecast based on the last two 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 forecasting. 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 given on an hourly basis.

Similarly, this formula can show temp figures for different time zones.

Example #2

Performing Linear Interpolation to know the sales of an organization in 2018

Let us 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 7,844 units in the first 25 days of the year, 16,094 units in 50 days of the year, etc.

We can use the same formula we used in interpolation to forecast sales value for different days, which we did not mention in the data we considered. Here the sales are in a straight line(linear) as we had taken cumulatively.

If we want to see the number of sales we had achieved in 215 days, we can get the forecasted number of sales for 215 days as below by considering the given sales data.

Similarly, we can determine the number of sales in that year by forecasting between the given points.

Things to Remember

  • It is the least accurate method, but it is fast and precise if the table values are closely spaced.It also can be used in estimating values for geographical data points, rainfall, noise levels, etc.It is very easy to use and not very accurate for nonlinear functions.Apart from Excel linear interpolation, we also have different methods like polynomial interpolation, spline interpolation, etc.

This article is a guide to Linear Interpolation in Excel. Here, we will see how to perform interpolation and linear interpolation in Excel, some examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Formula of Relative ChangeComplete list of Excel FunctionsForecast Formula in ExcelFormula for Standard Normal Distribution