Share on facebook
Share on twitter
Share on pinterest

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.

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

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. 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.

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 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.

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.

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.

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

Share on facebook
Share on google
Share on twitter
Share on linkedin

Leave a Reply

Your email address will not be published. Required fields are marked *