How to Use Linear Interpolation in Excel

This guide will discuss how to use linear interpolation in Excel

Excel is an excellent tool to utilize when performing statistical calculations. Since it contains several built-in functions and features we can use, it makes statistical analyses easier. And we can efficiently perform most of the statistical methods we need to. 

So linear interpolation is a mathematical process or statistical method that calculates new data points based on existing data points or information. Moreover, the entire process involves analyzing the know data points to understand the relationship between the x and y coordinates on the graphs. 

Once we have understood and analyzed the x and y coordinates, we can use them to estimate or create new coordinates. And there are two ways we can use linear interpolation in Excel.

Firstly, we can simply input the basic mathematical formula for linear interpolation. Secondly, we can use the FORECAST.LINEAR function in Excel.

Let’s take a sample scenario wherein we need to use linear interpolation in Excel.

Suppose we received a file containing the sales details of the company in 2020. So we have the data on the days and their sales in cumulative. And we want to forecast the sales value for different days by performing linear interpolation in Excel.

Furthermore, we can simply type the basic mathematical formula of linear interpolation to get the sales. Otherwise, we can utilize the FORECAST.LINEAR function in Excel.

Great! Before we move on, let’s learn how to write the FORECAST.LINEAR function in Excel.

 

The Anatomy of the FORECAST.LINEAR Function

The syntax or the way we write the FORECAST.LINEAR function is as follows:

=FORECAST.LINEAR(x, known_ys, known_xs)

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we begin any function in Excel.
  • FORECAST.LINEAR() is our FORECAST.LINEAR function. And this function is used to predict or calculate a future value along a linear trend by using the existing selected values.
  • x is a required argument. So this refers to the data point for which we want to predict or calculate a value. Additionally, this must be a numeric value.
  • known_ys is another required argument. And it refers to the dependent array or range if numeric data. 
  • known_xs is also a required argument. And it refers to the independent array or range of numeric data. Furthermore, the variance of known_x must not be zero. 

Amazing! Let’s also learn the syntax of the OFFSET function in Excel.

 

The Anatomy of the OFFSET Function

The syntax or the way we write the OFFSET function is as follows:

=OFFSET(reference, rows, cols, [height], [width])

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we start any function in Excel.\
  • OFFSET() refers to our OFFSET function. And this function is used to return a reference to a range that is a given number of rows and columns from the selected reference.
  • reference is a required argument. And it refers to the reference from which we want to base the offset. So this can be a cell reference or a range of adjacent cells.
  • rows is another required argument. So it is the number of rows, up or down, that we want the upper-left cell of the returned results to refer to.
  • cols is also a required argument. And this refers to the number of columns, to the left or right, that we want the upper-left cell of the returned results to refer to.

Now let’s dive into a real example of using linear interpolation in Excel.

 

A Real Example of Using Linear Interpolation in Excel

Let’s say we have a data set containing two columns of values. So the first column contains the x values while the second column contains the y values.

And our initial data set would look like this:

Initial data set

 

Firstly, we can simply input the basic mathematical formula of linear interpolation in Excel to get the result. So the formula for linear interpolation is y = y1 + (x-x1)(y2-y1)/(x2-x1). Once we have our data set, we must create a chart based on those values. 

Then, we will designate the cells where we want to place the new values. Lastly, we simply need to type the linear interpolation formula.

Secondly, we can also utilize FORECAST.LINEAR function to perform linear interpolation in Excel. So the FORECAST.LINEAR function predicts a future value along a linear trend using existing values. Similarly, we first need to first create a chart based on the initial data. 

Next, we will designate a new cell to input the formula and result. Then, we will use FORECAST.LINEAR function with the OFFSET and MATCH function to get the result from linear interpolation.

For example, we want to find the y value paired with a new x value of 15. So we already have the x value for the measured values of 12 and 14. But, we do not have an x value for 15. And we can use linear interpolation to find the x value.

And our final data set would look like this:

Final data set

 

You can make your own copy of the spreadsheet above using the link attached below. 

Finally, we can learn the steps of how to use linear interpolation in Excel.

 

How to Use Linear Interpolation in Excel

In this section, we will explain the step-by-step process of how to use linear interpolation in Excel. Furthermore, we will be discussing two methods of using linear interpolation. To apply this method to your work, you can simply follow the steps below.

1. Firstly, we need to create a chart based on our existing data values. To do this, we will select our data set and go to the Insert tab. Then, we will click the Scatter chart.

Linear Interpolation in Excel

 

2. Secondly, we will perform linear interpolation by using the basic formula. In this case, we want to find the x value of 15. So we will choose a new cell to input the formula “=14 + (15-12)*(18-14)/(16-12)”. Then, we will press the Enter key to return the result.

Furthermore, we can check the result by plotting the new values in the chart. If the new values match perfectly in the existing chart, the result is correct.

Linear Interpolation in Excel

 

3. Thirdly, let’s try doing linear interpolation using the built-in functions of Excel. Similarly, we will first create a chart based on the initial data. So we will go to the Insert tab and select the Scatter dropdown menu. Lastly, we will choose to display a chart.

Linear Interpolation in Excel

 

4. Next, we will input the formula “=FORECAST.LINEAR(15, OFFSET(C2:C6, MATCH(15, B2:B6, 1) -1, 0, 2), OFFSET(B2:B6, MATCH(15, B2:B6, 1) -1, 0, 2))” in a new cell. Then, we can press the Enter key to return the result.

Linear Interpolation in Excel

 

5. And tada! We have successfully used linear interpolation in Excel.

Final result

 

And that’s pretty much it! We have discussed how to use linear interpolation in Excel using two easy and simple ways. So you can now simply choose between inputting the basic formula or using the built-in functions to perform linear interpolation in Excel.

Are you interested in learning more about what Excel can do? You can now use the FORECAST.LINEAR function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like