We can use interpolation in financial analysis to estimate missing values. In engineering, estimation of variables like temperature or pressure can also be done. Moreover, Excel interpolation can aid environmental studies for spatial data estimation. It can also come in handy in scientific research to analyze experimental data, and in market research to fill gaps in survey responses
In this article, you will learn the process of doing basic linear interpolation in Excel and gradually the process of tackling more complex non-linear datasets. If you’ve ever wondered how to fill in missing values or estimate data points between known values, this guide will show you how to do it effectively using Excel’s user-friendly features.
We know that visual aids can make a world of difference in understanding concepts, which is why we’ve included a section on creating interpolation graphs right within Excel. We’re breaking it down step-by-step so that anyone, regardless of their statistical background, can grasp the concept.
But it’s not just about learning the technique; we will also show you how you can implement your learning into practical cases that arise in the real world.
Lastly, we will prepare you for any challenges that might pop up. We’re not just showing you how to do interpolation, but also how to avoid common mistakes so that your interpolation journey is as smooth as possible.
Here we have used Excel 365 to show you these Excel interpolation techniques.
Click the image for a detailed view
Download Practice Workbook
What Is Interpolation?
Interpolation is a mathematical technique used to estimate values within a set of known data points. It involves filling in the gaps between observed data points by assuming a smooth or linear relationship between them. Interpolation can be linear or non-linear, depending on the nature of the data and the desired accuracy.
How to Do Linear Interpolation in Excel?
You can do Linear Interpolation by following the instructions below for both linear and non-linear datasets.
How to Do Linear Interpolation for Linear Dataset in Excel?
If your dataset is linear, you can implement any linear interpolation method. Each time, the results will be the same. The image below displays a set of formulas that you can use to perform linear interpolation on a linear set of data.
Click the image for a detailed view
How to Do Linear Interpolation for Non-linear Dataset in Excel? (Taking All X & Y Values into Consideration)
If your dataset is non-linear, you may get different interpolation results if you follow different methods. You can perform linear interpolation either by taking all the X & Y values or only the adjacent X & Y values. Below are some methods that show you the process of considering all X & Y values.
1. Using FORECAST Function
- We’ll use the following formula in C15:
=FORECAST(C14,C5:C12,B5:B12)- Press Enter.
2. Using SLOPE and INTERCEPT Functions
- First, we need to calculate the dataset’s slope and intercept using formulas containing SLOPE and INTERCEPT functions.
For Slope = SLOPE(C5:C12,B5:B12)
For Intercept = INTERCEPT(C5:C12,B5:B12)
- Now, Inside cell C15 type the following formula:
=E6*C14+E8- Press Enter.
(N.B: This method follows the straight line equation, y = mx + c)
3. Using Excel Scatter Plot (Linear Trendline)
- Select the X and Y coordinate values and click Insert >> Charts >> Scatter.
- Click on Chart Elements. Select Trendline. Deselect Gridlines. Suitably fix the chart title.
- Double-click on the Trendline. Select Trendline Options >> Linear >> Display Equation on Chart.
The linear equation of the dataset is visible now. Inside the equation, you can put any X value and get the corresponding Y value.
Click the image for a detailed view
- Inside cell C17 put the following formula:
=0.7337*C16 + 4.0858- Press Enter.
How to Do Linear Interpolation for Non-linear Dataset in Excel? (Taking Adjacent X & Y Values into Consideration)
To do linear interpolation for a non-linear dataset by taking the adjacent X & Y values into account, follow any of the methods below.
1. Using General Mathematical Interpolation Formula
- We’ll use the following formula in C15:
=C7+(C14-B7)*(C8-C7)/(B8-B7)
- Press Enter.
2. Using FORECAST Function
We have used the FORECAST function before to do interpolation. But previously, the FORECAST function took all the X and Y values as input. In this method, only the adjacent X and Y values are taken.
There are two ways to find the adjacent X, and Y values from the data table. You can either use the combination of INDEX and MATCH functions or the XLOOKUP function.
Formulas for INDEX & MATCH
- For X1 :
=INDEX(B5:B12,MATCH(C14,B5:B12,1)) - For X2 :
=INDEX(B5:B12,MATCH(C14,B5:B12,1)+1) - For Y1 :
=INDEX(C5:C12,MATCH(C14,B5:B12,1)) - For Y2 :
=INDEX(C5:C12,MATCH(C14,B5:B12,1)+1)
Formulas for XLOOKUP
- For X1 :
=XLOOKUP($C$14, B5:B12,B5:B12,,-1,1) - For X2 :
=XLOOKUP($C$14, B6:B13,B6:B13,,1,1) - For Y1 :
=XLOOKUP($C$14, B5:B12,C5:C12,,-1,1) - For Y2 :
=XLOOKUP($C$14, B5:B12,C5:C12,,1,1)
- Now, Inside cell C15 type the following formula:
=FORECAST(C14,F8:F9,F6:F7)- Press Enter.
Click the image for a detailed view
3. Combining FORECAST, OFFSET, and MATCH Functions
- Inside cell C15 put the following formula:
=FORECAST($C$14, OFFSET($C$5:$C$12,MATCH($C$14, $B$5:$B$12,1)-1, 0,2), OFFSET($B$5:$B$12,MATCH($C$14, $B$5:$B$12,1)-1,0,2))- Press Enter.
Formula Breakdown
- MATCH($C$14, $B$5:$B$12, 1): Finds the position of the largest value less than or equal to the lookup value in the range B5:B12.
- OFFSET($B$5:$B$12, MATCH($C$14, $B$5:$B$12, 1)-1, 0, 2): Creates a new range by shifting the range B5:B12 by the number of rows specified by the result of the MATCH function minus 1, with a resulting range height of 2 rows.
- OFFSET($C$5:$C$12, MATCH($C$14, $B$5:$B$12, 1)-1, 0, 2): Creates a new range by shifting the range C5:C12 by the number of rows specified by the result of the MATCH function minus 1, with a resulting range height of 2 rows.
- FORECAST($C$14, OFFSET($C$5:$C$12, MATCH($C$14, $B$5:$B$12, 1)-1, 0, 2), OFFSET($B$5:$B$12, MATCH($C$14, $B$5:$B$12, 1)-1, 0, 2)): Performs linear interpolation using the FORECAST function, using the value in cell C14 as the X value and interpolating between two adjacent data points based on their corresponding X and Y values (shifted using OFFSET).
How to Do Non-linear Interpolation in Excel?
Doing non-linear interpolation on a non-linear dataset can help you obtain more accurate results than the previous ones. You can follow any of the methods below to do a non-linear interpolation in Excel.
How to Do Non-linear Interpolation Using GROWTH Function?
To perform a non-linear interpolation using the GROWTH function follow the instructions below.
- Write the following formula inside the cell C15:
=GROWTH(C5:C12,B5:B12,C14)- Press Enter.
How to Do Non-linear Interpolation Using Scatter Plot with Trendline?
To do a non-linear interpolation using a scatter plot with a trendline in Excel, follow the steps below.
- Select the X and Y coordinate values and click Insert >> Charts >> Scatter.
- Click on Chart Elements. Select Trendline. Deselect Gridlines. Suitably fix the chart title.
- Double-click on the Trendline. Select Trendline Options >> Exponential >> Display Equation on Chart.
The linear equation of the dataset is visible now. Now inside the equation, you can put any X value and get the corresponding Y value.
- Inside cell C17 put the following formula:
=4.2258*EXP(0.0936*C16)- Press Enter.
Click the image for a detailed view
What Are Some Practical Examples to Perform Interpolation in Excel?
Below are some practical examples where you might need to do interpolation in Excel.
How to Use FORECAST.LINEAR Function for Finding Density?
Follow the instructions below to use FORECAST.LINEAR function for finding density. It is important to note that, FORECAST.LINEAR determines a prediction using a straight line that fits your known data.
- Initially, choose cell F5 >> insert the below formula >> hit Enter.
=FORECAST.LINEAR(F4,C5:C11,B5:B11)
How to Apply TREND Function to Get Interpolated Density?
You can follow the steps below to apply the TREND function to get interpolated density.
- First, choose cell F5 >> insert the following formula >> hit Enter.
=TREND(C5:C11,B5:B11,F4,TRUE)
How to Do Interpolation With SLOPE and INTERCEPT Functions?
The SLOPE function finds the slope of a straight line. And, The INTERCEPT function calculates the point where the line crosses the y-axis. To combine these 2 functions to do interpolation follow the instructions below:
- First, choose cell F5 >> insert the following formula >> hit Enter.
=F4*SLOPE(C5:C11,B5:B11)+INTERCEPT(C5:C11,B5:B11)
How to Prevent Interpolation Errors in Excel?
We often get #DIV/0 when interpolating because x1 and x2 are the same. To prevent this error from occurring in the first place, let’s raise such an Error intentionally.
- First, choose cell F11 >> insert the below formula >> hit Enter to see #DIV/0.
=FORECAST(F10,F7:F8,F5:F6)
Now, to avoid errors like #DIV/0, we can use an improved formula.
- First, select cell F11 >> apply the following formula >> hit Enter.
=IF(F5=F6,F7,FORECAST(F10,F7:F8,F5:F6))
Thus, #DIV/0 will vanish.
What Are the Things to Keep in Mind?
- Make sure your data is organized in columns or rows, with the independent variable (X) values in one column and the dependent variable (Y) values in another column.
- Ensure that your X values are sorted in ascending order. Excel’s interpolation functions assume sorted data. If your data is not sorted, the interpolation results may be incorrect.
- Make sure there are no duplicate X values in your dataset. Excel’s interpolation functions rely on unique X values. If you have duplicate X values, the interpolation may not produce accurate results.
Frequently Asked Questions
1. Is there an interpolation function in Excel?
Answer: Yes, Excel provides several functions that can be used for interpolation. The most commonly used function for linear interpolation is the FORECAST function. For non-linear interpolation, Excel does not have a built-in function, but you can use other functions like TREND, GROWTH, or LINEST to perform various types of curve fitting and extrapolation.
2. What are the differences between extrapolation and interpolation?
Answer: Interpolation is the technique of estimating values within the range of known data points. It involves using the existing data to predict values that fall between the observed data points.
Extrapolation, on the other hand, involves estimating values beyond the range of known data points. It involves extending the trend or relationship observed in the existing data to predict values outside the range.
3. Is any Interpolated data a reliable source of information?
Answer: Interpolated data should be approached with caution as it involves estimating values between known data points, potentially introducing inaccuracies or biases. Its reliability depends on the quality of the original data and the appropriateness of the interpolation method used.
Conclusion
As you wrap up your journey through this article, I trust you’ve gained a solid grasp of Excel’s interpolation techniques. From basic linear interpolation to handling non-linear datasets, you’ve acquired the skills to bridge the gaps in your data seamlessly and tackle any error that comes your way.
Interpolation in Excel isn’t just about crunching numbers; it’s a skill that streamlines your tasks. By filling in gaps between known data points, you’re not only boosting efficiency in data entry and formulas but also elevating the accuracy of your insights.
Excel Interpolation: Knowledge Hub
- How to Interpolate Missing Data in Excel
- How to Do Linear Interpolation in Excel
- How to Do VLOOKUP and Interpolate in Excel
- How to Interpolate Between Two Values in Excel
- How to Do Interpolation with GROWTH & TREND Functions in Excel
- How to Use Non Linear Interpolation in Excel
- How to Interpolate in Excel Graph
- Do 2D Interpolation in Excel
- 3D Interpolation in Excel
- Linear Interpolation Excel VBA
- Calculate Logarithmic Interpolation
- Perform Exponential Interpolation
- Do Polynomial Interpolation
- Apply Cubic Spline Interpolation
- Interpolate Time Series
- Perform Bilinear Interpolation
<< Go Back to Excel for Statistics | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
