Excel - Bilinear and spline based interpolation functions within 2‑dimensional table

Interpolation within a 2-dimensional table is easier done by using a user defined function (UDF, built with VBA code) or LAMBDA function (defined directly within Excel's Name Manager, without VBA code) than by using regular Excel formulas.

bicubic
Source: wikipedia
 
     Download demo file  (58kB - downloaded 6541 times)
     Latest version: 2022-01-12


If you want to linearly interpolate within a 1-dimensional table, then you may also use a simpler function, see Linear interpolation.

Interpolate XY spline

Calculation explanation of above example - Bilinear interpolation

Visualisation of the data table above
Interpolate XY spline chart
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

Dependent on your Excel setting you need to use semicolon or comma in formulas. Best is to open the demo file and copy the formula from there, as it will contain your local settings. Else - if required - copy in function in text editor and replace comma by semicolon before copying into Excel.

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


LAMBDA function L_InterpolateX



Spline based 2-D interpolation - Main functions and sub functions VBA code