In Excel, interpolation allows us to get the value between two points on a graph or curve line. It is used to detect or anticipate the future value that lies between two existing data points. In our dataset, we have Weeks and Sales values. Sales records are for every alternate (Odd) week. We want to find an in-between sales value for week 8. Let’s see How to Interpolate in Excel Graph.
Interpolate in Excel Graph: 6 Ways
We will see six different methods for interpolating Excel graphs. We will get familiar with the functions TREND, SLOPE, INTERCEPT, FORECAST, GROWTH and use the simple mathematical equation for our calculation.
Method 1: Mathematical Equation for Linear Interpolation
- First of all, we will add a chart from the given dataset then we will use our mathematical function which is:
y= y1 + (x-x1)⨯(y2-y1)/(x2-x1)
Steps:
- After that, we will add a trendline to our graph, and it is apparent that we have linear growth data.
- Now, we have to select x1, x2, y1, and y2 from the given dataset. We will select above and below our X value, which is 8. So our x1 is x2 is, y1 is and y2 is
- Using these values, we will interpolate for week 8. Type the following formula in cell F13.
=F9+(F12-F7)*(F10-F9)/(F8-F7)
- Now, press the ENTER key.
- So, what we want is how to show this interpolated value in the graph. Right-click on the graph and click Select Data.
- After that, click Add from the popped-up dialogue box.
- What we have to do now is, to select the X and Y cells.
- Finally, click OK.
That’s it.
Method 2: Interpolate in Excel Graph Using Trendline
The trendline is one of the easiest methods for interpolating a linear equation.
Steps:
- We will add a trendline and graph from the dataset. In case you don’t remember how to do it, just follow Method 1.
- Now, right-click on the trendline and select Format Trendline.
- Now, select Display Equation on the Chart.
- As a result, we will see an equation in the chart.
- Now, type the following formula in F7.
=9.3631*F6 + 0.7202
- Finally, press the ENTER key.
- Now, follow Method 1, in case you forgot how to add interpolate data in the graph.
Method 3: Interpolate in Graph Using SLOPE and INTERCEPT Functions
Now, we will see the use of SLOPE and INTERCEPT functions.
Steps:
We will select the dataset, insert a graph and add a trendline to it as we have done in Method 1.
- Now, type the following formula in cell F7.
=SLOPE(C5:C12,B5:B12)*F6+INTERCEPT(C5:C12,B5:B12)
- After that, press the ENTER key.
- Our final graph chart will look like the following image after adding interpolate value in the graph.
- Follow Method 1, for adding interpolate values in an Excel chart.
Method 4: Using FORECAST Function
Interpolation is one kind of forecasting as we are predicting or anticipating the value. We don’t exactly know the real value exists. So, Excel in-built function FORECAST comes quite handy in this regard.
Steps:
- First, add a chart and trendline using the sample data. (Method 1, if you cannot recall the process )
- Then, type the following formula in cell F7.
=FORECAST(F6,C5:C12,B5:B12)
- Now, press the ENTER key.
- After adding, the value to chart our dataset will look like the following image.
Method 5: Interpolation Using TREND Function
Earlier we knew about the Trendline equation. Now, we will see the use of the TREND function.
Steps:
- First, we will add a chart and trendline as we did in Method 1.
- Now, type the following formula in cell F7.
=TREND(C5:C12,B5:B12,F6,1)
- After that, press the ENTER key.
Finally, add the interpolation value in the chart as we did earlier in Method 1.
Method 6: Interpolation Using GROWTH Functions
Excel has another built-in function called growth. GROWTH function is more reliable and accurate for the exponential and non-linear datasets. Suppose our dataset looks like the following.
Steps:
Insert Chart and add exponential trendline using. You can get help from Method 1 in case you are not familiar with the process.
- Type the following formula in cell F7.
=GROWTH(C5:C12,B5:B12,F6,2)
- Now, press the ENTER key.
- Then, add the interpolation value in the chart.
Read More: How to Do Interpolation with GROWTH & TREND Functions in Excel
Practice Section
The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.
Download Practice Workbook
Conclusion
That’s all for the article. These are 6 different methods of how to interpolate in Excel graph. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.
Related Articles
- How to Interpolate Missing Data in Excel
- How to Do Linear Interpolation in Excel
- How to Do VLOOKUP and Interpolate in Excel
- How to Interpolate Between Two Values in Excel
- How to Perform Bilinear Interpolation in Excel
- How to Use Non Linear Interpolation in Excel
- How to Do Linear Interpolation Excel VBA
<< Go Back to Excel Interpolation | Excel for Statistics | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!