Meraz Al Nahian
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.
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.
Read More: How to Interpolate in Excel Graph (6 Methods)
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.
Read More: How to Do Linear Interpolation in Excel (7 Handy Methods)
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 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))
- 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 the 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.
Read More: How to Do Interpolation with GROWTH & TREND Functions in Excel
Similar Readings
- How to Calculate Logarithmic Interpolation in Excel (2 Easy Ways)
- Do Linear Interpolation Excel VBA (with Easy Steps)
- How to Do 2D Interpolation in Excel (2 Suitable Ways)
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. 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)
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 Perform Bilinear Interpolation in Excel (with Easy Steps)
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 Perform Exponential Interpolation in Excel (4 Easy Ways)
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 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 VLOOKUP and Interpolate in Excel (6 Ways)
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 into the comment box. And also, share your feedback on my article. This will help me enrich my upcoming articles.
