How to Do Linear Interpolation in Excel (7 Handy Methods)

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.


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 (x1,y1) and C (x2,y2) 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=y1+ (x-x1)*(y2-y1)/(x2-x1)


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.

Dataset


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.

How to Do Linear Interpolation in Excel Using the Equation of the Linear Interpolation

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.

How to Do Linear Interpolation in Excel Using the Equation of the Linear Interpolation

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

How to Do Linear Interpolation in Excel Using the Equation of the Linear Interpolation


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 known_x’s argument representing the independent range of data (Day).

How to Do Linear Interpolation in Excel Applying the FORECAST Function

Shortly, you’ll get the following output.

Applying the FORECAST Function


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.

Using the TREND Function

So, the output will look as follows.

Using the TREND Function

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

Utilizing the SLOPE and INTERCEPT Functions

Thus, the output will be as follows.

Utilizing the SLOPE and INTERCEPT Functions


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.

How to Do Linear Interpolation in Excel Combined Application of FORECAST, OFFSET, and MATCH Functions

And, you’ll get the following output.

Combined Application of FORECAST, OFFSET, and MATCH Functions

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. x1,x2 (for the independent range of data) and y1,y2 (for the dependent range of data).

The formula for determining x1:

=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 x1 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.

Using INDEX and MATCH Functions

The formula for determining x2:

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

Using INDEX and MATCH Functions

The formula for determining y1:

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

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

Using INDEX and MATCH Functions

The formula for determining y2:

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

How to Do Linear Interpolation in Excel Using INDEX and MATCH Functions

Finally, you need to use the FORECAST function to interpolate based on the found values of  x1,x2, and y1,y2.

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

How to Do Linear Interpolation in Excel Using INDEX and MATCH Functions

Eventually, you’ll get the following output.

How to Do Linear Interpolation in Excel Using INDEX and MATCH Functions


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 x1:

=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 (search_mode argument)

Using the XLOOKUP Function

The formula for determining x2:

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

Using the XLOOKUP Function

The formula for determining y1:

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

Using the XLOOKUP Function

The formula for determining y2:

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

How to Do Linear Interpolation in Excel Using the XLOOKUP Function

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

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

How to Do Linear Interpolation in Excel Using the XLOOKUP Function

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 known_x’s and known_y’s are empty, you’ll get a  #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.


Related Article

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo