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.
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)
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)
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
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))
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)
- 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))
- 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)
- 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)
- 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.
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)
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.
- 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.
- 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.
- 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.
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)
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
- How to Do Interpolation with GROWTH & TREND Functions in Excel
- How to Do Linear Interpolation in Excel
- How to Perform Bilinear Interpolation in Excel
- How to Use Non-Linear Interpolation in Excel
- How to Do VLOOKUP and Interpolate in Excel
- How to Do Linear Interpolation Excel VBA
<< Go Back to Excel Interpolation | Excel for Statistics | Learn Excel