Excel - Linear interpolation function

Linear interpolation (interpolating within a 1-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 (UDF) interpolateX. The VBA code of this function is found at the end of this page. This function works both for ascending and descending header data, and both for horizontally or vertically organized data. (If you want to interpolate both X and Y values within a 2-dimensional table, then see Bilinear interpolation. If you want to create a curve that goes exactly through all your data points in order to interpolate, then see splines interpolation.)

Download demo file   (37kB - downloaded 1037 times).

New in Excel: LAMBDA functionality. LAMBDA functions are user defined functions created directly within Excel. They execute faster than UDF. If you have an Excel version that supports LAMBDA: the LAMDA function L_InterpolateX is found below as well.

Calculation explanation of above example

Visualisation of data table

X = 470 lies between X = 400 and X = 500. X-fraction = (470 - 400)/(500 - 400) = 0.70

The 2 data point values to interpolate between are:
     1. (400) = 100
     2. (500) = 150

     100 + 0.70 × (150 - 100) = 135.00 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.

LAMBDA function L_InterpolateX to be put in Name Manager