Excel - Bilinear interpolation function &
spline based 2-dimensional interpolation functions

Interpolating within a 2-dimensional table can be done with regular MS Excel functions. But it will become a very long formula, that is hard to read and/or copy across. You can also implement:
  1. VBA - User Defined Function (UDF) InterpolateXY. Bilineair interpolation function. VBA code is found further below.
  2. LAMBDA function L_InterpolateXY. Bilinear LAMBDA interpolation function. LAMBDA functionality is new in Office365/Excel 2021 (still being rolled out). It enables you to directly create a function within Name Manager. Function codes are found further below.
  3. VBA - User Defined Function (UDF) - spline based interpolation functions. Non-linear, spline based interpolation functions. A spline is a curve that goes through the data points (see also Excel splines). In the background, splines in X direction are created which are input for a spline in Y direction that brings the final interpolation value. This brings a more accurate result, especially if your data table is about a convex or concave curved surface. You can choose from three types of splines:
     Download demo file  (58kB - downloaded 4451 times)
     Latest version: 2022-01-12 - Negative gradient issue repaired. Three spline based interpolation functions added.

(If you want to interpolate within a 1-dimensional table, then see Linear interpolation.)



Calculation explanation of above example - Bilinear interpolation

Visualisation of the data table above
Calculation
X = 451 lies between X = 400 and X = 500. X-fraction = (451 - 400)/(500 - 400) = 0.51
Y = 75 lies between Y = 60 and Y = 80. Y-fraction = (75 - 60)/(80 - 60) = 0.75

The 4 data point values to interpolate between are (X,Y):
     1. (400,60) = 149
     2. (500,60) = 163
     3. (400,80) = 169
     4. (500,80) = 187

Interpolation X:
     1.&2. → 149 + 0.51 × (163 - 149) = 156.14
     3.&4. → 169 + 0.51 × (187 - 169) = 178.18

Interpolation Y:
     156.14 + 0.75 × (178.18 - 156.14) = 172.67 = final bilineair interpolation result.

The Cubic spline based interpolation brings 173.44, which is 0.4% higher. If your data is about a concave curved surface, it is obvious that the bilinear interpolation result will always slightly underestimate, except on a data point itself. The above example is slightly convex in Y direction, but more dominantly concave in X direction.

Bilinear interpolation - VBA code to be put in a module

In MS Excel, press Alt+F11 to open the Visual Basic Editor (Windows). Via top menu: Insert → Module. Copy the code below into the module.



LAMBDA function L_InterpolateXY

Based on L_InterpolateX, so you need to copy in both functions.

LAMBDA function L_InterpolateX



Non-linear, spline based interpolation - Main functions and sub functions VBA code