How to Interpolate in Excel
If you’re into mathematics and need to discover unknown data points from a survey sample of known data points, interpolating with Excel is an effortless choice.
Excel is a robust calculator with data visualization. Thus, for various mathematical and statistical analyses, you’ll always find it useful. Though there is no straightforward function for interpolation, you can use various functions to estimate new data points from a given data table of X and Y values.
Read on to know the popular and easy data interpolation methods in Excel.
1. Using a Mathematical Equation
One of the basic approaches of data interpolation in Microsoft Excel is to apply the mathematical formula of linear interpolation. You can use this method if getting a linear trendline for a chart.
Y= Y1 + (X-X1)⨯(Y2-Y1)/(X2-X1)
For example, you want to find out the value of Y when X is 15 from this table. Here is how:
- Create a table of X1, X2, Y1, and Y2 values by analyzing the table.
- You have to choose the data points one step above and below the new X-value that you’re looking for.
- Now, apply the following formula in any cell to find the interpolated value of Y that corresponds to the X-value 15.
The process is pretty simple and manual. For larger data sets, you’ll have to invest a lot of time in interpolating the data points.
Download: Interpolate in Excel Spreadsheet (Free)
2. Using the Curve Fitting Method
Curve fitting is another quick yet manual method to interpolate with Excel. Just create a scatter plot and generate the graph equation. Excel utilizes linear regression to populate the equation. You need to apply this curve fit equation to find the value of Y:
Y= Slope * X + Y-intercept
To discover the value of Y from a given value of X, which is 15, you can try these steps:
- Select the table and insert a scatter chart.
- Right-click on the trendline and then select Format Trendline.
- Check the boxes beside Display R-squared value on chart and Display Equation on chart.
- Also, choose the Linear trendline.
- Once the scatter graph reveals the equation, fit the value of X into it to find the value of Y.
- The following formula reveals the value of Y.
The curve fitting method is only suitable for small data sets. There is no easy automation for this process, and you can only rely on the Microsoft Excel macros for hot-key-based automation.
3. Using Excel Functions: Slope and Intercept
You can utilize the SLOPE and INTERCEPT functions in Excel for linear interpolation of the Y value. You can easily apply these functions since they only require the values of known Xs and Ys. The following are the syntaxes that you’ll need to use:
Here is how you can interpolate data points via this method:
- Rename two cells with Slope and Intercept.
- Apply the SLOPE function beneath the Slope header and choose the relevant data from the table.
- Similarly, apply the INTERCEPT function and populate the value of intercept.
- Now, apply the Y= Slope * X + Y-intercept formula to discover the value of Y.
- You can create an Excel formula for the above equation, where you can simply change the X’s value to find out other unknown data points.
You can interpolate faster if you make cells for the X-value, Slope, and Intercept. However, this process may not fetch accurate data for non-linear interpolation.
4. Using Excel Function: FORECAST
It’s a popular Excel function for predicting performance by analyzing a set of real-world data points. You’ll find FORECAST and FORECAST.LINEAR in Excel 2016 and later editions. It’s good to use FORECAST.LINEAR for reliable results.
The function utilizes three arguments to produce a predicted data point. These arguments are a given value for X, known values of Y, and values of Xs from a table. Check out the syntax below:
=FORECAST.LINEAR(x, known_y's, known_x's)
You can quickly determine the value of Y by following these steps:
- The cell where you want to populate the value for Y should have an equal (=) sign.
- Now, type Forecast and select FORECAST.LINEAR function from the formula drop-down menu.
- Click on the X’s value and put a comma.
- Then select the known Ys’ value cell range followed by a comma.
- Select the known Xs’ cell range and close the formula with a parenthesis.
- Press Enter to retrieve the interpolated value of Y.
- You can keep on changing the value of X within the range of the given table to fetch Y-values.
- The FORECAST formula of this tutorial is as follows:
The FORECAST.LINEAR functions offer highly accurate data prediction for linear and slightly nonlinear data points. It utilizes the first and last values for a given X-value and takes into account the entire data set.
Therefore, you’ll see the utilization of this function for mission-critical data analytics like forecasting web traffic, modeling financial data, and calculating business performance.
5. GROWTH Function for Nonlinear Interpolation
When you’ve got a table of real-world data in exponential form, FORECAST.LINEAR won’t yield accurate interpolated data points. Excel has another excellent function for you to predict data, and that’s GROWTH. Like other functions mentioned above, this formula is also easy-to-apply.
Apart from the familiar arguments like known Xs’ cell range, known Ys’ values, and the given value of X, GROWTH utilizes an additional logical value. Usually, you need to enter one for this logical constant. Here is how the syntax looks:
To interpolate data in Excel by using the GROWTH function, you may follow these steps:
- Insert the GROWTH function in any cell where you want the interpolated value for Y.
- Enter the cell range for known Y-values followed by a comma.
- Repeat the above step to input the cell range for known X-values.
- Now, enter the value of given X and put a comma.
- Enter 1 as the last argument and close the formula.
- Hit Enter to generate the value of interpolated Y.
- You can use cell references instead of actual values to discover more values by simply changing the given data.
- The final formula will look like the following:
When compared to the FORECAST function, GROWTH generates more reliable and accurate interpolated data points for exponential data sets that are mostly nonlinear. Similar to FORECAST, you can also automate the GROWTH function without applying much effort.
Data Interpolation and Forecasting Made Easy
You can now efficiently interpolate data for your upcoming tasks from work or school.
Such quick data insights help you in various ways, like cutting a few repetitive steps of data research or estimating your task load before taking up any more projects.
Want to impress your audience with actionable data insights and compelling visualizations? Check out these Google Data Studio features.
About The Author