What is Interpolation and How To Interpolate In Excel

How to interpolate in Excel

Interpolation is a useful mathematical and statistical method; used to estimate an unknown value of a function between two known values. In this blog, we are going to learn about interpolation and how it’s done in Excel.

Table of Content

What Is Interpolation?

Suppose we have a function with discrete points, and we need a value between two specific values of the function. To estimate this unknown value, we should interpolate. In other words, interpolation is estimating an unknown value between two known values.

The data we are working with is sometimes linear, or approximately linear. In this case, linear interpolation is useful to predict an unknown value. When our data is not linear, linear interpolation does not give an accurate estimation. So we have to look for another solution.

Linear Interpolation

Linear interpolation is the crossing of a straight line between two data points. For example, suppose we have a dataset of x and y values. We need to know the y value corresponding to x=18.1. We will introduce four ways to determine this value. Suppose, we name this point B; we’ll also call the two points that we need for interpolating, A(7,14) and C(9,19).

Linear interpolation example

Mathematical Equation for Linear Interpolation

The mathematical equation for this case is as follows:

y= y1 + (x-x1)⨯(y2-y1)/(x2-x1)

We need the value of y corresponding to x, which makes point B(x,y). A(x1,y1) and C(x2,y2) are the two points around B. To find the required y, type the equation above in an Excel cell. For this example:

x1 = 7 , y1 = 14

x2 = 9 , y2 = 19

Look at the following image:

Linear interpolation equation in Excel

Linear Interpolation Using Trendline

To interpolate a value using this method, we should draw a graph for the data. Then Excel fits a line according to the data and gives its equation through a process called liner regression. Once we have the equation, we can find the value of y  by putting x in it. To

  1. Draw the scatter chart of data
  2. Add linear trendline to the chart

Design > Add Chart Element > trendline > linear

How to add linear trendline in Excel

You can also use icon beside the chart and check the trendline.

3. Get the equation derived from the data by double-clicking on the trendline graph > trendline options > check “Display Equation on chart.”

4. Put x in the equation

How to interpolate using linear trendline in Excel

Note: Trendline fits a line through all of the points. We can use the trendline for the two points around the required value. By doing so, Excel crosses a line between points A and C, and that’s what we did in the first method.

SLOPE and INTERCEPT Functions

As mentioned above, the trendline feature uses linear regression to find an equation for the dataset. Now, what if you don’t want to draw the graph and go through the whole process?

Well, there is an alternate method. The SLOPE function is used to calculate the slope of the linear regression equation, and the INTERCEPT function is used to calculate the intercept of the linear regression equation.

To use these functions for linear interpolation, in our example, type the following equation in a cell:

=SLOPE(C2:C15,B2:B15)*F2+INTERCEPT(C2:C15,B2:B15)

How to interpolate using SLOPE and INTERCEPT functions in Excel

Read the How to Use LINEST Function in Excel blog, to learn more about the SLOPE and INTERCEPT functions.

Related Post

FORECAST Function

The FORECAST or FORECAST.LINEAR function predicts a value based on linear regression. The FORECAST function takes three arguments.

FORECAST(x,known_y’s,known_x’s)

x : the value for which we want to predict the corresponding y value

known_y’s: the dependent values of the function (y values)

known_x’s: the independent values of the function (x values)

Note: If you are using a version of Excel older than the 2016 version, you can only use the FORECAST function. Microsoft Excel added FORECAST.LINEAR in the 2016 version. It is suggested to use the FORECAST.LINEAR function.

To interpolate using the FORECAST function in our example, type the following equation in a cell:

=FORECAST.LINEAR(F2,C2:C15,B2:B15)

How to interpolate using the FORECAST function in Excel

Possible Errors

#N/A: This error will occur if the size of the first and second data sets is not equal, or if one or both sets are empty.

#DIV/0: This error will occur if the variance of the independent values is equal to zero.

#VALUE: This error will occur if the input x is non-numeric.

Click on the link, if you want to know more about Excel formula errors and why they happen. 

Related Post

TREND Function

TREND function gives an unknown value based on linear regression. The syntax of the TREND function is as bellows:

TREND(known_y’s,[known_x’s],[new_x’s],[const])

known_y’s: The set of known y values

[known_x’s]: The range of cells or arrays of x values (optional)

If it’s ignored, Excel will assume the x values are (1,2,3, so on)

[new_x’s]: The value for which we want to predict corresponding y value

This argument is optional too. If the [new_x’s] argument is omitted, it is set to be equal to the [known_x’s].

[const]: this is the function’s logical argument. 1 means that in the equation y=ax+b, b will be calculated normally. And 0 means it is set to zero.

Note: although these arguments are optional when we need to interpolate to find out an unknown value, we have to use them.

How to interpolate using the TREND function in Excel

Nonlinear Interpolation

When the data is not linear, the first thing we have to do is to find out the function’s behavior.

Nonlinear Interpolation Using Trendline

Excel can fit exponential, logarithmic, and polynomial equations to the data. Let’s discuss it with an example:

Nonlinear interpolation example dataset

We have a dataset of x and y values, and we want to predict the corresponding value to x = 18.1. First of all, we need to find out the best equation that can describe our data. So we use trendline using these steps:

1. Draw the chart

2. Add different types of trendline (exponential, logarithmic, and polynomial) to the chart

Design > trendline > more trendline options.

In the “format trendline” window, select the trendline type and check “Display R-squared value on chart.”

How to add the best nonlinear trendline equation in Excel

At this step, we should choose the best equation that matches the data. We can understand this using R-squared. The more R-squared, the more the equation matches the data. If the R-squared value is less than 0.9, it is better not to use the equation because it would not be accurate enough.

3. Check display equation on chart

4. Put x in the equation.

How to do nonlinear interpolation using trendline in Excel

GROWTH Function

If the data is exponential, we can use the Excel GROWTH function.

The GROWTH function predicts a value based on exponential regression. It supposes the equation in the form of y = b*m^x

The GROWTH function syntax is as bellows:

GROWTH(known_y’s,[known_x’s],[new_x’s],[const])

known_y’s: The set of known y values

[known_x’s]: The range of cells or arrays of x values

[new_x’s]: The value for which we want to predict corresponding y value

[const]: This is a logical value. 1 means the function will calculate b normally, and 0 means b is supposed to be 1.

To interpolate using growth function in our example, type the following equation in a cell:

=GROWTH(C2:C15,B2:B15,F2,1)

How to do nonlinear interpolation using GROWTH function in Excel

Possible Errors

#REF!: This error will occur if the size of the datasets is not equal.

#VALUE!: This error will occur if the data set’s values are non-numeric, or the logical input for the “linear constant” is not correct.

Related Post

FAQ

1. What is the interpolation function in Excel?

Depending on what type of interpolation you need, Excel provides different functions to use. As we discussed in the blog, in the case of linear interpolation, you can use the FORECAST function or TREND function. When your data behaviour is exponential, the GROWTH function is useful.

2. What is the difference between interpolation and regression?

Regression is the process to find the line or curve that best fits the dataset. After you find the line, you can calculate the missing value using the line’s (or curve) equation. This second process is an interpolation.

3. What is the difference between interpolation and extrapolation?

When you predict a value in the range of your dataset, you are interpolating, while extrapolation is used when you want to predict a value beyond your dataset.

4. Which is more accurate interpolation or extrapolation?

During extrapolation, our aim is to estimate a value outside the range of data. To do this, we assume that data continues its trend, and this might not be true. But in interpolation, we want to calculate an unknown value within the range of data.
So we can state that the value calculated from interpolation is more valid than the one calculated from extrapolation.

Sum Up

Every time you need to interpolate, the first thing you have to do is to check the data’s behavior. If it’s linear, you can interpolate using one of the four methods we introduced. If they’re not linear, you can interpolate using one of the two methods we introduced for nonlinear interpolation.

You can connect with us and ask our experts for your inquiries and get more Excel Support Service.Reduce cost, accelerate tasks, and improve quality with Excel Automation Service.

Subscribe to our Newsletter

Share this post with your friends

One Response

Leave a Reply

Your email address will not be published.