How to Interpolate Between Two Values in Excel (6 Ways)

Data interpolation is a very important thing when we analyze an experiment or we want to predict or determine the results of an event. For instance, if we have data on two different occasions, we can determine the data between those occasions by interpolation. This article will briefly describe 6 methods on how to interpolate between two values in Excel. In the dataset, we have some X Coordinates and Y Coordinates.

how to interpolate between two values in excel


Download Practice Workbook


6 Ways to Interpolate Between Two Values in Excel

1. Applying FORECAST/FORECAST.LINEAR Function to Interpolate Between Two Values in Excel

The simplest way to interpolate between two values is to use the FORECAST/FORECAST.LINEAR function. Let’s go through the description below.

Steps:

  • Determine which value you want to interpolate and make new rows for the value you want to interpolate and the interpolated In this case I want to interpolate between 8 and 9 so I chose a value of 8.5.

  • Now type the following formula in cell B15.
=FORECAST(C14,C5:C12,B5:B12)

how to interpolate between two values in excel

Here, the FORECAST function determines the interpolated value in cell C15 via linear regression. It works on the ranges B5:B12 (as known_Xs) and C5:C12 (as known_Ys).

  • Hit the ENTER button and you will see the interpolated value in cell C15.

  • You can also use the LINEAR function in this case. Just put FORECAST.LINEAR instead of FORECAST in the formula.
=FORECAST.LINEAR(C14,C5:C12,B5:B12)

how to interpolate between two values in excel

  • Hit ENTER and you will see the same interpolated value as before.

Thus you can easily interpolate between two values using the FORECAST function.

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


2. Using the Combination of Excel XLOOKUP and FORECAST Functions to Interpolate Between Two Values

If you want to interpolate within a small range of the dataset, you can use the XLOOKUP function to extract the coordinates in the range and interpolate a value within them. Suppose we want to interpolate the value 6 in B9:C10. Let’s discuss the procedure below.

Steps:

  • First, make some modifications to the dataset to place the coordinates.

how to interpolate between two values in excel

  • Type the following formula in cell F7.
=XLOOKUP(C14, B5:B12,B5:B12,,-1,1)

The XLOOKUP function looks up the value in C14, searches for this value in the range B5:B12, and returns the value which is adjacently smaller than 6.5 as it cannot find this exact value in that range and we put -1 in this regard. Thus we get x1 as 5.

A similar formula is used several times in this section. When we need a value adjacently larger than 6.5, we used ‘1’ instead of ‘-1’ in the formula.

  • Hit ENTER to see the result in cell F7.

how to interpolate between two values in excel

  • Now type the following formula in cell F8.
=XLOOKUP(C14, B5:B12,B5:B12,,1,1)

  • Hit the ENTER key and you will see a larger value than 6 in cell F8.

how to interpolate between two values in excel

  • After that, type the following formula in cell F9.
=XLOOKUP(C14, B5:B12,C5:C12,,-1,1)

  • Hit ENTER. This operation will deliver you the value in cell C9.

how to interpolate between two values in excel

  • Then type the formula in cell F10.
=XLOOKUP(C14, B5:B12,C5:C12,,1,1)

  • Hit ENTER and you will see the Y Coordinate of cell C10.

how to interpolate between two values in excel

  • After that, select cell C15 and type the formula given below.
=FORECAST(C14,F9:F10,F7:F8)

  • Press the ENTER key to see the interpolated value in cell C15.

how to interpolate between two values in excel

Thus you can interpolate between two values within a small range.

Read More: How to Do VLOOKUP and Interpolate in Excel (6 Ways)


3. Applying INDEX and MATCH Functions with FORECAST Function to Interpolate Between two Values

We can also interpolate between two values within a small range of the dataset by using the INDEX and MATCH functions to extract the coordinates in the range and interpolate a value within them. Suppose we want to interpolate the value 6 in B9:C10. Let’s discuss the procedure below.

Steps:

  • First, make some modifications in the dataset to place the coordinates.

  • Type the following formula in cell F7.
=INDEX(B5:B12,MATCH(C14,B5:B12,1))

how to interpolate between two values in excel

Here the MATCH function returns the position of the cell value of C14 in the range B5:B12. And then the INDEX function returns the value of that position in B5:B12. Thus it returned x1. 

A similar formula is used in this section several times to determine x2, y1, and y2.

  • Hit ENTER to see the result in cell F7.

  • Now type the following formula in cell F8.
=INDEX(B5:B12,MATCH(C14,B5:B12,1)+1)

how to interpolate between two values in excel

  • Hit the ENTER key and you will see the larger value than 6 in cell F8.

  • After that, type the following formula in cell F9.
=INDEX(C5:C12,MATCH(C14,B5:B12,1))

how to interpolate between two values in excel

  • Hit ENTER. This operation will deliver you the value in cell C9.

  • Then type the formula in cell F10.
=INDEX(C5:C12,MATCH(C14,B5:B12,1)+1)

how to interpolate between two values in excel

  • Hit ENTER and you will see the Y Coordinate of cell C10.

  • After that, select the cell C15 and type the formula given below.
=FORECAST(C14,F9:F10,F7:F8)

how to interpolate between two values in excel

  • Press the ENTER key to see the interpolated value in cell C15.

Thus you can interpolate between two values within a small range.


4. Interpolating Between Two Values Utilizing a Mathematical Formula 

Another effective method to interpolate between two values will be to apply a mathematical formula. The interpolation formula is given below.

how to interpolate between two values in excel

This is an equation of a straight line. Let’s follow the necessary instructions to apply it to interpolate between two values.

Steps:

  • Make some modifications in the dataset for interpolation value and type the following formula in cell C15. Here we want to find the interpolated value when the X Coordinate is 75. And for this reason, we are going to take X Coordinates which are adjacently smaller or greater than 2.75 and their corresponding Y Coordinates in this dataset.
=C7+(C14-B7)*(C8-C7)/(B8-B7)

how to interpolate between two values in excel

The formula returns the interpolated value by measuring the slope of the 2 points which are (2.5, 4) and (3, 6).

  • Now just hit ENTER to see the interpolated value in cell C15.

Thus you can interpolate between two values just by using a mathematical equation.


 (6 Methods)5. Interpolation between Two Values by SLOPE and INTERCEPT Functions

One of the easiest ways to interpolate between two values is to use Excel SLOPE and INTERCEPT functions and use their results into a straight-line formula. Let’s see the procedure below. We want to interpolate the X Coordinate 10.

Steps:

  • Make some changes in your dataset to store the slope.

how to interpolate between two values in excel

  • Type the following formula in cell E7
=SLOPE(C5:C12,B5:B12)

The SLOPE function returns the slope/gradient of the linear regression line which is made by the points formed by given X and Y Coordinates.

  • Hit ENTER and you will see the slope of these data in cell E7.

how to interpolate between two values in excel

  • Now type the following formula in cell E9 to find the Y-intercept.
=INTERCEPT(C5:C12,B5:B12)

The INTERCEPT function returns the Y-intercept of the linear regression line which is made by the points formed by given X and Y Coordinates.

  • Hit ENTER to see the output in cell E9.

how to interpolate between two values in excel

  • Insert this formula in cell C15.
=E7*C14+E9

The formula is a basic straight line formula which is y=mx+c.

  • Hit ENTER to see the interpolated value in cell C15.

how to interpolate between two values in excel

Thus you can determine the interpolated value between two values by using Excel SLOPE and INTERCEPT functions.


6. Using GROWTH Function for Nonlinear Interpolation

The GROWTH function is a special function to interpolate non-linear data. Our dataset basically consists of non-linear relations between Y and X Coordinates. So it would be best to use this function.

Steps: 

  • Make some changes to store the interpolated We want to interpolate a value between 5 and 8. Let it be 6.5.

  • Type the following formula in cell C15.
=GROWTH(C5:C12,B5:B12,C14)

how to interpolate between two values in excel

Here the GROWTH function returns the interpolated data by predicting exponential growth of the X and Y Coordinates.

  • Hit ENTER and you will see the interpolated value in cell C15.

Thus you can find interpolated values by using the GROWTH function. As this function works for non-linear relations, you can have more accurate data as a result of its help.

Read More: How to Do Interpolation with GROWTH & TREND Functions in Excel


Practice Section

In the following image, you will see the dataset of this article. I put this in so that you can practice on your own.

how to interpolate between two values in excel


Conclusion

The bottom line is, that this article will help you understand how to interpolate between two values in Excel. By interpolation, you can determine the exact or most accurate result for data that is not included in an experiment which is really important in scientific and statistical affairs. If you have any better methods of interpolating between two values in Excel, please leave them into the comment box. And also, share your feedback on my article. This will help me enrich my upcoming articles.


Related Article

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo