How to Interpolate in Excel Graph (6 Methods)

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.

How to interpolate in excel graph


Download Practice Workbook


6 Ways to Interpolate in Excel Graph

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:

  • Select the entire data and go to INSERT > Scatter.

How to interpolate in excel graph using formula

  • After that, we will add a trendline to our graph and it is apparent that we have got 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

How to interpolate in excel graph using formula

  • Using these values we will interpolate for week 8. Type the following formula in cell F13
=F9+(F12-F7)*(F10-F9)/(F8-F7)

How to interpolate in excel graph using formula

  • Now, press the ENTER key.

How to interpolate in excel graph using formula

  • So, what we want is, how to show this interpolated value in the graph. Right-click on the graph and click Select Data.

How to interpolate in excel graph using formula

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

How to interpolate in excel graph using formula

That’s it.

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


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.

How to interpolate in excel graph using trendline

  • Now, select Display Equation on the Chart.

  • As a result, we will see an equation in the cart.

How to interpolate in excel graph using trendline

  • Now, type the following formula in F7.
=9.3631*F6 + 0.7202

How to interpolate in excel graph using trendline

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

How to interpolate in excel graph using slope and intercept

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

How to interpolate in excel graph by FORECAST function

  • After adding, the value to chart our dataset will look like the following image.

Read More:


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)

How to interpolate in excel graph by TREND Function

  • 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 inbuilt function called growth. GROWTH function is more reliable and accurate for the exponential and non-linear dataset. Suppose, our dataset looks like the following.

How to interpolate in excel graph using GROWTH

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)

How to interpolate in excel graph FOR non linear data

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

How to interpolate in excel graph


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

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo