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

## 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}`)`

## Linear Interpolation in Excel: 7 Methods

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.

**Read More: **How to Do Linear Interpolation Excel VBA

### 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 combined 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.

### 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

## 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.

**Download Practice Workbook**

## 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.