Cubic and Bicubic Interpolation Excel Functions

Cubic and Bicubic Interpolation Excel Functions v3

We have expanded our custom Excel interpolation functions to interpolate both 2D and 3D datasets, i.e. to interpolate over a curve or over a surface.

Cubic and Bicubic Interpolation Excel Examples with Source Code

Cubic Interpolation in Excel

CubicInterpolation() is a spreadsheet formula that interpolates over 2D data.  The first argument is a list of x points and the second argument is the list of corresponding y points.  These x and y values can be ranges on the worksheet or arrays (if you use the function directly in VBA).  They can be in rows or in columns.  One can be in a row and the other in a column; this function is very forgiving about how the input is formatted.  The example below shows that the interpolation function ignores blank cells at the end of the dataset as in Rows 12-14).

The blue points are the original source data, which are irregularly spaced (note the gaps near x=2.4 and x=2.8) and far apart.  The dotted lines represent piecewise linear interpolation.  The red dots are points we get from CubicInterpolation() at various locations.  These tend to mimic the curvature of the function that would be lost with any kind of linear interpolation.  This is one of the benefits of using cubic interpolation.

The technique it’s using is piecewise Lagrange interpolation.  When you try to interpolate at x, the script looks for the two points in your source data on either side of x.  From those four points it constructs a cubic equation by Lagrange interpolation.  In other words, the fitting is piecewise cubic.  The benefit of piecewise curve fitting is that you can work with highly complicated datasets that do not need to look anything like a cubic function because each fit is local and uses the best datapoints.  This helps avoid Runge’s phenomenon (wild oscillations from overfitting).

 

Bicubic Interpolation in Excel

BicubicInterpolation() interpolates over 3D tabular data, and this older post gives details about how to use it.  In summary, the first function argument is the entire table of axes and data like in the example below.  The second and third arguments give the location within the table where you want to interpolate.  This too is very forgiving with regard to its arguments and where/how it is used.  It works as a spreadsheet function or directly in VBA.

The algorithm is similar to the 2D case, except that instead of interpolating over four nearby points, it interpolates locally over 16 points.  The second argument is the “top point,” meaning the location along the horizontal axis at the top of the table.  The third argument is the “left point,” similarly referring to the axis at the left side of the table.  In summary, the syntax follows the pattern: table, x, y.  The intent is to be intuitive insofar as the horizontal axis coming before the vertical axis.

With these functions you can do 2D and 3D interpolation in Excel as if you’re using a built-in spreadsheet function.