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:

IP2

CSpline2