
MS Excel VBA - Bilinear interpolation function
Bilinear interpolation (interpolating within a 2-dimensional table) can be done with regular MS Excel functions. But it will become a pretty long formula, that is hard to read and/or copy across. You can also implement a user defined function interpolateXY. The VBA code of this function is found at the end of this page.Download demo file (17kB - downloaded 1530 times)

Calculation explanation of above example
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 which is the final result.
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 which is the final result.
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.