This article demonstrates the process of doing 3D interpolation in Excel. Basically, we have created a customized UDF (User defined function) based on VBA to do this. More importantly, we have prepared a 3D graph based on the outputs.

3D interpolation in Excel is a powerful technique that enables users to estimate missing values or predict outcomes at unobserved data points within a three-dimensional grid. With its significance in data analysis, this method becomes a valuable tool for dealing with sparse datasets, whether they involve spatial data, time series, or any other three-dimensional data format. By using VBA code, users can bridge the gap between existing data points, gain a comprehensive understanding of their data, and make more informed decisions with confidence.

**Download Practice Workbook**

## 3D Interpolation in Excel: 4 Easy Steps

Let’s learn how to do 3D interpolation in Excel with 4 easy steps.

### Step 1: Finding the Value of Z

- First, we have to find out the value of the
**Z**from the**Z = SIN(x**equation. To apply the equation, we have used the following formula in the^{2}+y^{2})/√(x^{2}+y^{2})**D5**cell.

`=SIN((B5^2+C5^2))/(B5^2+C5^2)^0.5`

Here,

**B5**=**X**value**C5**=**Y**value

The formula resembles Mexican Hat function. In the above formula, the **SIN function** returns the sine of given values.

### Step 2: Creating UDF with VBA

- Now, we are going to demonstrate how we have written the function.
- First, we select
**Developer**>>**Visual Basic**.

- Select
**Insert**>>**Module**.

- We have to write down the code below.

- You can copy the code from here.

```
Function TrilinearInterpolation(inputX As Double, inputY As Double) As Double
Dim xValues() As Variant
Dim yValues() As Variant
Dim zValues() As Variant
Dim fValues() As Variant
' Assuming your data is in B5:D17
xValues = Range("B5:B24").Value
yValues = Range("C5:C24").Value
zValues = Range("D5:D24").Value
Dim nx As Integer, ny As Integer, nz As Integer
nx = UBound(xValues)
ny = UBound(yValues)
nz = UBound(zValues)
Dim i As Integer, j As Integer
For i = 1 To nx - 1
If inputX <= xValues(i, 1) Then Exit For
Next i
If i = nx Then i = nx - 1
For j = 1 To ny - 1
If inputY <= yValues(j, 1) Then Exit For
Next j
If j = ny Then j = ny - 1
Dim x0 As Double, x1 As Double, y0 As Double, y1 As Double
x0 = xValues(i, 1)
x1 = xValues(i + 1, 1)
y0 = yValues(j, 1)
y1 = yValues(j + 1, 1)
Dim xd As Double, yd As Double
xd = (inputX - x0) / (x1 - x0)
yd = (inputY - y0) / (y1 - y0)
Dim c00 As Double, c01 As Double, c10 As Double, c11 As Double
c00 = zValues(i, 1)
c01 = zValues(i, 1)
c10 = zValues(i + 1, 1)
c11 = zValues(i + 1, 1)
Dim z0 As Double, z1 As Double
z0 = c00 * (1 - yd) + c10 * yd
z1 = c01 * (1 - yd) + c11 * yd
TrilinearInterpolation = z0 + (z1 - z0) * xd
End Function
```

**🔎 Code Explanation**

**Function Definition:** The code defines a function called TrilinearInterpolation that takes two input arguments (inputX and inputY) of type Double and returns a Double value as the result of the trilinear interpolation.

`Function TrilinearInterpolation(inputX As Double, inputY As Double) As Double`

**Declaring Variables: **Several arrays (xValues, yValues, zValues, and fValues) and other variables are declared to hold the input data and intermediate values during the interpolation process.

```
Dim xValues() As Variant
Dim yValues() As Variant
Dim zValues() As Variant
Dim fValues() As Variant
```

**Assigning Data:** The code assumes that the data for the interpolation is stored in the range B5:D24 of the Excel sheet. The data in columns B, C, and D is copied into the respective arrays (xValues, yValues, and zValues) using the Range().Value method.

```
xValues = Range("B5:B24").Value
yValues = Range("C5:C24").Value
zValues = Range("D5:D24").Value
```

**Determining Array Sizes:** The variables nx, ny, and nz are used to store the upper bound of the arrays xValues, yValues, and zValues, respectively. These values represent the number of data points available along each axis.

```
Dim nx As Integer, ny As Integer, nz As Integer
nx = UBound(xValues)
ny = UBound(yValues)
nz = UBound(zValues)
Dim i As Integer, j As Integer
For i = 1 To nx - 1
If inputX <= xValues(i, 1) Then Exit For
Next i
If i = nx Then i = nx - 1
For j = 1 To ny - 1
If inputY <= yValues(j, 1) Then Exit For
Next j
If j = ny Then j = ny - 1
```

**Finding the Interval for X: **The code uses a loop to find the interval where inputX falls between xValues(i, 1) and xValues(i+1, 1). It iterates over the array xValues and compares inputX with each element until it finds the interval where inputX lies. It then stores the index i representing the lower bound of the interval. If inputX is greater than or equal to the last element of xValues, i is set to nx – 1.

```
Dim x0 As Double, x1 As Double, y0 As Double, y1 As Double
x0 = xValues(i, 1)
x1 = xValues(i + 1, 1)
```

**Finding the Interval for Y:** Similarly, the code uses another loop to find the interval where inputY falls between yValues(j, 1) and yValues(j+1, 1). It iterates over the array yValues and compares inputY with each element until it finds the interval where inputY lies. It then stores the index j representing the lower bound of the interval. If inputY is greater than or equal to the last element of yValues, we set j to ny – 1.

```
y0 = yValues(j, 1)
y1 = yValues(j + 1, 1)
```

**Calculating Interpolation Parameters: **The code calculates interpolation parameters xd and yd for inputX and inputY, respectively. xd represents the relative position of inputX within the interval determined by xValues(i, 1) and xValues(i+1, 1). Similarly, yd represents the relative position of inputY within the interval determined by yValues(j, 1) and yValues(j+1, 1).

```
Dim xd As Double, yd As Double
xd = (inputX - x0) / (x1 - x0)
yd = (inputY - y0) / (y1 - y0)
Dim c00 As Double, c01 As Double, c10 As Double, c11 As Double
c00 = zValues(i, 1)
c01 = zValues(i, 1)
c10 = zValues(i + 1, 1)
c11 = zValues(i + 1, 1)
```

**Calculating Interpolation for Z:** The code uses the trilinear interpolation method to calculate z0 and z1 by combining data from four corners of a cube formed by c00, c01, c10, and c11. These corner values are the zValues corresponding to the positions (x0, y0), (x0, y1), (x1, y0), and (x1, y1) respectively. The z0 and z1 values are calculated using bilinear interpolation in the x-y plane.

```
Dim z0 As Double, z1 As Double
z0 = c00 * (1 - yd) + c10 * yd
z1 = c01 * (1 - yd) + c11 * yd
```

**Final Trilinear Interpolation: **The code calculates the final result using xd to interpolate between z0 and z1. This step combines the interpolated values from the x and y directions to obtain the final interpolated value for the given inputX and inputY. The result is assigned to the function and returned as the output of the **TrilinearInterpolation **function.

### Step 3: Prepare Dataset According to X and Y value

- Already we created a
**TrilinearInterpolation**function using VBA to create 3D interpolation from the dataset. Now, it’s time to apply the function. So, the formula will be in the**H4**

**
**

`=Trilinearinterpolation(G4,H3)`

Here,

**G4**= **X **values, and** H3**=**Y **values.

### Step 4: Create 3D Interpolation Graph

To get a better visualization of the outputs, we can create a 3D graph.

- Select the data and click on
**Recommend Charts**

- Next, select
**All Charts**>> choose**Surface**>> pick**3D Surface**chart >> finally, press**OK**.

- Now we will get the interpolated 3D graph.

## Things to Remember

- Take more data points for your function, so it can predict all the data well.
- Whenever possible, compare the interpolated results with actual data points to validate the accuracy of your interpolation. This step is essential to ensure the reliability of your predictions.

## Frequently Asked Questions

**1. What are the benefits of 3D interpolation in Excel?**

3D interpolation allows users to fill in missing data points within a dataset, enabling a more complete analysis and visualization. It helps in making accurate predictions and provides a smoother representation of the data, particularly when dealing with sparse datasets.

**2. How can I deal with outliers or noisy data in 3D interpolation?**

Handling outliers or noisy data is crucial for accurate 3D interpolation. Preprocessing techniques such as data smoothing, data filtering, or outlier removal, we can apply before interpolation to improve the reliability of results.

## Conclusion

In conclusion, the use of VBA code for 3D interpolation in Excel represents a significant achievement in data analysis and visualization. By enabling the estimation of missing values and predicting outcomes within a three-dimensional dataset, this customized and dynamic approach adds a valuable tool to the Excel user’s repertoire. The ability to perform advanced interpolation empowers users to gain deeper insights and make more informed decisions, making it an essential asset for data analysts and researchers.