You can interpolate or estimate values between two known data points in Excel. For instance, if you have quarterly sales data but want to estimate monthly sales, you can use interpolation to generate the desired monthly data points.
Additionally, interpolation can help you approximate and fill in missing values in a dataset so you can insert charts and analyze the data easily.
There are several types of interpolation for different purposes. However, in this tutorial, I will only show you how to interpolate in Excel when data has linear and exponential relationships.
Interpolate in Excel When Data Has Linear Relationship
In this section, I show you how to do linear interpolation in Excel.
In linear interpolation, you assume that the change between two known data points is linear, meaning one variable changes at a constant rate relative to the other.
Method #1.1: Use the Linear Interpolation Formula
You can use the linear interpolation formula to interpolate missing values in a dataset with a linear relationship.
Below is the formula for linear interpolation:
- (x1, y1) and (x2, y2) are the existing data points.
- x is the value at which you want to interpolate.
- y is the estimated value at x.
Consider the dataset below, where column A represents x-values, the time it takes for an athlete to walk a certain number of miles (y-values), shown in column B. You want to interpolate the missing distance value in cell B6.
To estimate the missing value y at x = 3.25, you can linearly interpolate between the points (2.75,11) and (3.5,14).
For x = 3.25:
- x1 = 2.75, y1 = 11
- x2 = 3.5, y2 = 14
You can plug the cells containing the values above into the linear interpolation formula to interpolate the missing value, as shown in the formula below:
=11+(3.25-2.75)*(14-11)/(3.5-2.75)
The interpolated value of y when x = 3.25 is 13.
Note: If you have multiple x-values for which you need y-values, you can drag the formula down to copy it to other cells.
Method #1.2: Use the FORECAST.LINEAR Function
Although the FORECAST.LINEAR function is mainly used for extrapolation, computing a future value along a linear trend using known values, you can also apply it for interpolation.
The syntax of the function:
FORECAST.LINEAR (x, known-ys, known_xs)
The ‘x’ argument is required and specifies the value you want to interpolate at.
The ‘known-ys’ argument is required and is the set of existing dependent (y) values.
The ‘known-xs’ argument is required and is the set of existing independent (x) values.
Consider the dataset below, where column A shows the time it takes for an athlete to walk a certain number of miles (x-values), shown in column B (y-values). You want to estimate the missing value in cell B6.
You can use the formula below to estimate the missing value:
=FORECAST.LINEAR(A6,B2:B7,A2:A7)
The above formula estimates the missing value as 13. You can copy it and paste it as a value in cell B6.
Note: The FORECAST.LINEAR function is only available in Excel 365 and later. You can use the FORECAST function if you have an older version of Excel.
Method #1.3: Use the TREND Function
You can use the TREND function to interpolate in Excel linearly. The function, only available in Excel 365 and later, calculates and predicts values along a linear trendline.
The syntax of the function:
=TREND(known_ys, [known_xs], [new_xs], [const])
The ‘known_ys’ argument is required and is the set of existing dependent (y) values.
The ‘known_xs’ argument is optional and is the array of existing independent (x) values. It is assumed to be {1, 2, 3, …} if omitted.
The ‘new_xs’ argument is optional and is the new x-values you want to predict y-values for.
The ‘const’ argument is optional and is a logical value indicating whether to force the y-intercept to be 0. If TRUE or omitted, Excel calculates the intercept normally. If FALSE, Excel sets the intercept to 0.
Consider the dataset below, where column A shows the time it takes for an athlete to walk a certain number of miles, shown in column B. You want to extrapolate the distance walked in column B at the value of 3.25 hours in column A.
You can use the formula below to estimate the distance the athlete can walk in 3.25 hours:
=TREND(B2:B6,A2:A6,3.25)
The interpolation estimates the distance the athlete can walk in 3.25 hours as 13 miles. You can enter the new data point in the right position in your dataset.
Method #1.4: Use a Chart’s Linear Trendline Equation
You can use a chart’s linear trendline equation to interpolate in Excel linearly.
Consider the dataset below, where column A shows the time it takes for an athlete to walk a certain number of miles, shown in column B. You want to interpolate at the time of 3.25 hours in column A.
You can use the steps below to estimate the desired value:
- Select the data.
- Click the Insert tab, open the ‘Insert Scatter (X, Y) or Bubble Chart’ drop-down menu on the ‘Charts’ group, and select the ‘Scatter’ option.
The above step inserts a scatter plot on the worksheet.
- Select the scatter plot, click the green cross icon on the top right corner and select ‘Trendline’ on the ‘Chart Elements’ menu.
The above step displays a trendline on the scatter plot.
- Right-click the trendline and select ‘Format Trendline’ on the shortcut menu.
The above step opens the ‘Format Trendline’ task pane on the right of the Excel window.
- Check the ‘Display Equation on chart’ check box on the ‘Format Trendline’ task pane.
- The above step displays the equation ‘y = 4x’ on the scatter plot’s trendline.
You can now use the trendline equation y = 4x to interpolate the distance the athlete can walk in 3.25 hours as follows:
y = 4 * 3.25
y = 13
This method estimates the desired y-value as 13 when the x-value is 3.25. You can enter the new data point in an appropriate row in your dataset, as shown below.
Interpolate in Excel When Data Has Exponential Relationship
In this section, I show you how to do exponential interpolation in Excel.
In exponential interpolation, you assume that one variable changes at a rate proportional to its current value leading to the variable increasing or decreasing exponentially rather than linearly over time.
For instance, in population growth, the larger the population gets, the faster it grows. Similarly, in radioactive decay, the quantity decreases at a rate proportional to its current amount.
Method #2.1: Use Exponential Interpolation Formula
You can use the exponential interpolation formula to interpolate missing values in a dataset with an exponential relationship.
Below is the exponential interpolation formula:
- (x1, y1) and (x2, y2) are the existing data points.
- x is the value at which you want to interpolate.
- y is the estimated value at x.
Suppose you have the dataset below showing the population growth in a particular region over time. You want to interpolate the missing population value in cell B6.
To estimate the missing value y at x = 4, you can exponentially interpolate between the points (3,1162) and (5,1284).
For x = 4:
- x1 = 3, y1 = 1162
- x2 = 5, y2 = 1284
You can plug the cells containing the values above into the exponential interpolation formula, to interpolate the missing value as shown in the formula below:
=B5 * (B7/B5)^((A6 - A5)/(A7 - A5))
The above formula estimates the missing value as 1,221.
Method #2.2: Use the GROWTH Function
You can use the GROWTH function to interpolate in Excel exponentially. The function calculates values in an exponential growth trend matching existing data points.
The function’s syntax:
GROWTH(known_ys, [known_xs], [new_xs], [const])
The explanation of the arguments is the same as those for the TREND function mentioned in Method #1.3 above.
Suppose you have the dataset below showing the population growth in a particular region over time. You want to interpolate the population value at year 4.
You can use the formula below to interpolate the desired population value:
=GROWTH(B2:B6,A2:A6,4)
The above formula estimates the population value in year 4 as 1,221. You can enter the new data point in the dataset in the appropriate row, as shown below.
Method #2.3: Use a Chart’s Exponential Trendline Equation
You can use a chart’s exponential trendline equation to interpolate in Excel exponentially.
Suppose you have the dataset below showing the population growth in a particular region over time. You want to interpolate the population value at year 4.
Here’s how to do it:
- Select the entire dataset.
- Click the Insert tab, open the ‘Insert Scatter (X, Y) or Bubble Chart’ drop-down menu on the ‘Charts’ group, and select the ‘Scatter with Smooth Lines and Markers’ option.
The above step inserts a scatter plot on the worksheet.
- Select the scatter plot, click the green cross icon on the top right corner, click the right arrow next to the ‘Trendline’ option on the ‘Chart Elements’ menu, and select ‘Exponential’ on the sub-menu.
The above step displays an exponential trendline on the scatter plot.
- Right-click the trendline and select ‘Format Trendline’ on the shortcut menu.
The above step opens the ‘Format Trendline’ task pane on the right of the Excel window.
- Select the ‘Display Equation on chart’ check box on the ‘Format Trendline’ task pane.
The above step displays an exponential equation on the chart’s trendline, as shown below:
Excel gives you the exponential equation y=999.88e0.5x so you can interpolate y for x = 4 by entering the formula below in a cell:
=999.88*EXP(0.05*4)
This method estimates the desired y-value as 1221 when the x-value is 4. You can enter the new data point in an appropriate row in your dataset as shown below.
I have shown several ways to interpolate in Excel. I hope you found the tutorial helpful.
Other Excel articles you may also like:
