This guide will discuss **how to use linear interpolation in Excel**.

##### Table of Contents

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:

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:

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.

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.

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.

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.

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

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.