3D Interpolation in Excel: 4 Easy Methods

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 Excel sheet’s range B5:D24. 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 store the upper bound of the arrays xValues, yValues, and zValues. 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: 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 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 cube corners 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). 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.