﻿ Excel - Bilinear and spline based interpolation functions within 2-dimensional table (LAMBDA & UDF)

# 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.

Source: wikipedia
• Bilineair interpolation functions InterpolateXY (UDF) and L_InterpolateXY (LAMBDA equivalent). LAMBDA functionality exists since Office365/Excel 2021. It enables you to create new functions directly within Excel's Name Manager, without VBA code. A LAMBDA function executes faster than a VBA based UDF.
• Three spline based 2D-interpolation functions (UDF). A spline is a curve that goes through the data points (see also Excel splines). More accurate for convex/concave surfaces, as bilinear interpolation always overestimates/underestimates, except on datapoints itself.
• InterpolateXY_CubicSpline: also known as bicubic interpolation
• InterpolateXY_MonotoneCubicSpline: based on monotone cubic splines that do not 'wobble' like the other two types of splines
• InterpolateXY_CardinalSpline: based on a cardinal spline with parameter tension between 0.0 and 1.0 (default 0.5; at 0.0 it 'becomes' bilinear interpolation).

If you want to linearly interpolate within a 1-dimensional table, then you may also use a simpler function, 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

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.