How to Do 2D Interpolation in Excel: 2 Suitable Ways

Method 1 – Perform Interpolation of Linear 2D Data in Excel

1.1 Apply Mathematical Equation for Linear Interpolation

There is an established mathematical equation for linear equations we will use.

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

STEPS:

Find out the coefficients of this equation.
Place the values of x1, x2, y1, y2, and x in the Excel sheet.

Finding out coefficients of mathematical equation

We want to find out the sales for the week of 8. So, x = 8. In the dataset, there is sales information for 7 and 9. So, x1 = 7 and x2 = 9. And y represents the corresponding sales of these weeks. For this reason, y1 = 670 and y2 = 890.

Use the following formula in the F12 cell:

=F7+(F11-F5)*(F8-F7)/(F6-F5)

Press Enter to observe the interpolated result.

Formula for Applying Mathematical Equation for Linear Interpolation

1.2 Interpolate Using a Trendline

STEPS:

Insert a plot from the dataset.
Click on the Insert tab.
Click on the drop-down menu of the Insert Scatter (X, Y) or Bubble Chart from the Charts group.
A wizard will open.
From the wizard, select the Scatter chart.

Insertion of Scatter Plot

The following chart is shown on the Excel Sheet.

Scatter Plot of sales dataset

Click on the chart.
You can see a plus (+) icon.
Click on this icon and check Trendline.

Adding Trendline for Excel 2D interpolation

A trendline can be seen on the chart.
Double-click on the trendline.
The Format Trendline window will appear on the right side of the Excel Sheet.
Check Display Equation on the chart.
You can see a floating equation.

Finding out the trendline equation in Excel

Find the value of y from this equation.
Use the following formula in the F6 cell:

=93.631*F5+7.2024

Press Enter to observe the interpolated result of sales.

Formula for Interpolate Using Trendline in Excel

1.3 – Interpolate 2D Data Using SLOPE and INTERCEPT Functions

There is an established mathematical equation for linear equations.

y= mx+c

STEPS:

Use the following formula in the F6 cell:

=SLOPE(C5:C12,B5:B12)*F5+INTERCEPT(C5:C12,B5:B12)

Press Enter to observe the interpolated result.

Formula for Interpolate 2D Data Using SLOPE and INTERCEPT Functions

Find out the sales for the week of 8. So, x = 8. “M” denotes the slope of the straight line. C represents the interception of the straight line with the vertical axis. With the SLOPE function, we have calculated the slope. The INTERCEPT function calculates the interception.

1.4 – Insert the FORECAST Function for Interpolation

STEPS:

Use the following formula in the F6 cell:

=FORECAST(F5,C5:C12,B5:B12)

Press Enter to observe the interpolated result.

Formula for Inserting FORECAST Function for Interpolation

1.5 – Interpolation of 2D Data Using the TREND Function

STEPS:

Use the following formula in the F6 cell:

=TREND(C5:C12,B5:B12,F5,1)

Press Enter to observe the interpolated result.

Formula for Interpolation of 2D Data Using TREND Function

In the TREND function, we have set the constant 1. The TREND function predicts the linear trend of the dataset.

Method 2 – Perform Interpolation of Non-Linear 2D Data in Excel

2.1 – Interpolation Using the GROWTH Function

STEPS:

Use the following formula in the F6 cell:

=GROWTH(C5:C12,B5:B12,F5,2)

Press Enter to observe the interpolated result.

Formula for Interpolation in 2D Using GROWTH Function

In the GROWTH function, we have set the constant 2.

2.2 – Combine FORECAST, INDEX, and MATCH Functions for Non-Linear Interpolation

STEPS:

Use the following formula in the F6 cell:

Formula for Combining FORECAST, INDEX, and MATCH Functions for Non-Linear Interpolation

Use the MATCH function to retrieve the positions of the value close to 8. We used the INDEX function to determine the cell reference for that value. We have created a dynamic range, and the FORECAST function predicts the interpolated value from this dynamic range.

Download the Practice Workbook

2D Interpolation.xlsx