Md. Abdul Kader
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.
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 (with Easy Steps)
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).
Shortly, you’ll get the following output.
Read More: How to Do 2D Interpolation in Excel (2 Suitable Ways)
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.
Read More: How to Interpolate in Excel Graph (6 Methods)
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. 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.
⧬ 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.
⧬ 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.
⧬ The formula for determining y2:
=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 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.
Eventually, you’ll get the following output.
Read More: How to Do VLOOKUP and Interpolate in Excel (6 Ways)
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)
⧬ 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.
⧬ 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.
⧬ The formula for determining y2:
=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 Perform Bilinear Interpolation in Excel (with Easy Steps)
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.
