Excel - Splines UDF

The demo file contains Cardinal Spline & Cubic Spline & Monotone Cubic Spline UDF that create curves that go exactly through all your data points. The advantage of a monotone cubic spline is that it does not 'wobble'. (If you want to interpolate both X and Y values within a 2-dimensional table, then see Bilinear interpolation and its demo file that also includes spline based interpolation functions.)

Download demo file   (135kB - downloaded 2028 times - Latest version: 2022-01-11, now including both regular function that returns a single Y value, given X and the datapoints, and array function that creates a table with X and Y values, given the number of segments to be created between the datapoints provided.)



MS Excel VBA code to be put in a module - array functions

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

Generic helper function to read range data into single dimensional array.
Your input data can now be column organized (default table) or row organized (transposed default table).


Cardinal spline


Cubic spline


Monotone cubic spline

Regular function - Single Y value output only, given X value

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

Cardinal spline


Cubic spline


Monotone cubic spline