Interpolation Update

This post was prompted by a question at Eng-Tips.  The question was looking for ways to plot stress contours under a footing, which requires interpolation to find the depth where the stress is at the contour values, given stresses calculated on a regular grid.

The simplest way is using linear interpolation, which can be done using the IP2 spreadsheet.  In the example shown below the data to be interpolated is in B19:C19, and the lines representing the contour stress levels are in B42:C51.  Note that the contour stress levels are entered as a continuous line covering the full extent of the stress data (X = 0 to X = 10).

IP Function Input (click for full size view)

IP Function, linear interpolation results

The main problem with this approach is that linear interpolation will not always pick up peaks in the data, for instance at stress levels near 100, at X = 3.

Better results are given by the SolveSplineA function, which can be found in the CSpline2 spreadsheet.  This function has been revised to return multiple interpolation values, rather than just the first in the specified range.  In this case the stress data is entered in A72:B92, and the contour values are simply listed in order in D72:D76.  It can be seen below that the function has found an additional 2 intersection points for the 100 contour, close to X = 3.

Reverse Cubic Interpolation with the SolveSplineA Function

The spreadsheets described above (including full open source code) may be downloaded from:



This entry was posted in Excel, UDFs, VBA and tagged , , , , , , . Bookmark the permalink.

1 Response to Interpolation Update

  1. Ben Bunday says:

    Excellent work! I’ve been trying to solve an interpolation problem for a couple days now to clone an algorithm for measuring line widths from SEM images, and was going with a piecewise linear interpolation approach, but kept running into issues of how to deal with multiple solutions; your code solves this issue and also improves to the cubic spline!!!

    Again, much thanks!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.