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

Get FREE Advanced Excel Exercises with Solutions!

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 for 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


1. Using 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.


2. Combining 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.


3. Inserting INDEX- MATCH 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 a 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 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 by Applying 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. 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.

Read More: How to Interpolate in Excel Graph


5. Interpolating between Two Values by Joining 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.

Read More: How to Interpolate Missing Data in Excel


6. Using Excel 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 the 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.


Download Practice Workbook


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 in the comment box. And also, share your feedback on my article. This will help me enrich my upcoming articles.


Related Articles


<< Go Back to Excel Interpolation | Excel for Statistics | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo