Sometimes you may need to create a new data point from the given range of known data points. In such a situation, you might accomplish linear interpolation in Excel. In this article, I’ll discuss 7 methods on how to do linear interpolation using the functions in Excel along with the basics.

**Table of Contents**hide

## Download Practice Workbook

## Basics of Linear Interpolation and Its Mathematical Equation

Unlike extrapolation, interpolation is a method of finding a value from the known values. In the case of linear interpolation, you might find the new data point from the two known data points.

Let’s say, you know the values of two known points A (x_{1},y_{1}) and C (x_{2},y_{2}) of a straight line. And, you want to guess the unknown value y from the point of B (x,y) when you also know the values of x. In that case, the well-used mathematical equation is as follows.

`y=y`

_{1}

`+ (x-x`

_{1}`)*(y`

_{2}`-y`

_{1}`)/(x`

_{2}`-x`

_{1}`)`

## 7 Methods to Do Linear Interpolation in Excel

Let’s introduce the following dataset where the **Number of Visits** is given on the basis of the **Day **of a well-known Excel learning website. Now, you need to interpolate the *Number of Visits* for a specific *Day*.

### 1. Using the Equation of the Linear Interpolation

In the beginning method, I’m going to show you how to do linear interpolation in Excel using the basic mathematical equation.

Suppose, you want to find the *Number of Visits* for *Day *number 55.

Just insert the following formula in the **G5 **cell.

`=D9+(F5-C9)*(D10-D9)/(C10-C9)`

Here, **D9 **and **D10 **are the *Number of Visits* for *Day *numbers 50 and 60 respectively whereas **C9 **and **C10 **are the cells having the values of the corresponding *Day*.

But why do you need to deal with **C9 **and **C10 **cells? Because you want to interpolate for *Day *50 and it lies between those cells.

After pressing **ENTER**, you’ll get the interpolated *Number of Visits* is 7383.

### 2. Applying the FORECAST Function to Do Linear Interpolation

Luckily, you may interpolate any values using the function in Excel. For example, you can apply the **FORECAST** or **FORECAST.LINEAR** function to find the *Number of Visits* for *Day *75.

`=FORECAST.LINEAR(F5,D5:D14,C5:C14)`

Here, the **F5 **represents the value for which you want to interpolate, **D5:D14** is the ** known_y’s** argument which is mainly the dependent range of data (

*Number of Visits)*and the

**C5:C14**is the

**argument representing the independent range of data (**

*known_x’s**Day*).

Shortly, you’ll get the following output.

### 3. Using the TREND Function

Likewise the **FORECAST **function, the **TREND** function finds the values based on a linear trend.

`=TREND(D5:D14,C5:C14,F5,1)`

Here, **1** is for defining the value of const is 0.

So, the output will look as follows.

**Read More:** **How to Do Interpolation with GROWTH & TREND Functions in Excel**

### 4. Utilizing the SLOPE and INTERCEPT Functions

Also, you can interpolate if you use the equation for the simple linear regression. That is **Y = a + bX **where **Y** and **X **are the dependent and independent variables respectively. Besides, **a** is the intercept and **b** is the slope value.

Fortunately, there are two dedicated functions i.e. **SLOPE **and **INTERCEPT** for computing the slope and intercept respectively. Please look at the following formula.

`=SLOPE(D5:D14,C5:C14)*F5+INTERCEPT(D5:D14,C5:C14)`

While computing the linear interpolation, you need to multiply the slope (as illustrated in the equation) with the independent variable (value of *Day *32).

Thus, the output will be as follows.

### 5. Combined Application of FORECAST, OFFSET, and MATCH Functions

Furthermore, you may utilize the **FORECAST**, **OFFSET**, and **MATCH** functions combinedly to interpolate a data point.

`=FORECAST(F5,OFFSET(D5:D14,MATCH(F5,C5:C14,1)-1,0,2),OFFSET(C5:C14,MATCH(F5,C5:C14,1)-1,0,2))`

In the above formula, **OFFSET(D5:D14,MATCH(F5,C5:C14,1)-1,0,2)** syntax specifies the reference for the dependent values (**known_y’s**). Here, the **MATCH **function finds the relative position of the lookup value (**F5** cell) for which you want to interpolate. Besides, **0** is the ** Cols** (column) argument of the

**OFFSET**function. It is zero as you are going to interpolate in the same column. Besides, the value of the

**Height**argument is 2 because you want to interpolate for the last 2 values.

Besides, the** OFFSET(C5:C14,MATCH(F5,C5:C14,1)-1,0,2)) **syntax returns the reference for the independent variables (**known_x’s**).

Lastly, the **FORECAST **function interpolates the *Number of Visits* for *Day *45.

And, you’ll get the following output.

**Read More:**** How to Interpolate Between Two Values in Excel (6 Ways)**

### 6. Using INDEX and MATCH Functions to Do Linear Interpolation

In addition, you may use the **INDEX** and **MATCH **functions to interpolate any new data point.

As I discussed in the mathematical equation of the linear interpolation, you may find those data points i.e. **x**** _{1}**,

**x**

**(for the independent range of data) and**

_{2}**y**

**,**

_{1}**y**

**(for the dependent range of data).**

_{2}⧬ *The formula for determining x*

_{1}*:*

`=INDEX(C5:C14,MATCH(G8,C5:C14,1))`

Here, **G8 **is the value of *Day *85 (**x**) for which you want to interpolate. The above formula finds the value of **x**** _{1}** where the

**MATCH**function returns the relative position of the lookup value.

**1**is used to find the lower value if the exact value is not found. Finally, the

**INDEX**function returns the matched value from the

**known_y’s**.

⧬ *The formula for determining x*

_{2}*:*

`=INDEX(C5:C14,MATCH(G8,C5:C14,1)+1)`

Here, **1** is added to find the higher value than the lookup value especially if exact matching is not found.

⧬ *The formula for determining y*

_{1}*:*

`=INDEX(D5:D14,MATCH(G8,C5:C14,1))`

The same thing happens here except for dealing with the independent range of data.

⧬ *The formula for determining y*

_{2}*:*

`=INDEX(D5:D14,MATCH(G8,C5:C14,1)+1)`

Finally, you need to use the **FORECAST **function to interpolate based on the found values of **x**** _{1}**,

**x**

**and**

_{2,}**y**

**,**

_{1}**y**

**.**

_{2}

`=FORECAST(G8,G11:G12,G9:G10)`

Here, **G11:G12** is the cell range of **known_y’s** and **G9:G10 **is for the **known_x’s**.

Eventually, you’ll get the following output.

### 7. Using the XLOOKUP Function to Do Linear Interpolation in Excel

For Microsoft 365 and Excel 2021 users, using the **XLOOKUP** function might be a different experience to interpolate a new data point.

⧬ *The formula for determining the value of ***x**_{1}*:*

`=XLOOKUP($G8,$C$5:$C$14,C$5:C$14,,-1,1)`

Here,** -1 (**the value of the ** match_mode** argument) is for finding the next smaller item and

**1**is for searching from the top of the array (

**e argument)**

*search_mod*⧬ *The formula for determining x*

_{2}*:*

`=XLOOKUP($G8,$C$5:$C$14,C$5:C$14,,1,1)`

Here, **1** (the value of the ** match_mode** argument) is for finding the next largest value.

⧬ *The formula for determining y*

_{1}*:*

`=XLOOKUP($G8,$C$5:$C$14,$D$5:$D$14,,-1,1)`

This formula is the same as the earlier one. The only exception is here i.e. dealing with the dependent range of data.

⧬ *The formula for determining y*

_{2}*:*

`=XLOOKUP($G8,$C$5:$C$14,$D$5:$D$14,,1,1)`

Lastly, use the **FORECAST **function to interpolate and you’ll get your desired output.

`=FORECAST(G8,G11:G12,G9:G10)`

**Read More:** **How to Do VLOOKUP and Interpolate in Excel (6 Ways)**

## Things to Remember

- If the value of the x is non-numeric, you’ll get
**#VALUE! Error**in the case of using the**FORECAST**function. - Besides, if the cells of
and*known_x’s*are empty, you’ll get a*known_y’s***#N/A**error.

## Conclusion

That’s the end of today’s session. From the beginning to the end, I discussed the 7 ways to do linear interpolation in Excel. I strongly believe this article will be highly beneficial for you. Anyway, if you have any queries or recommendations, please share them in the comments section below.