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).

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.

IP2

CSpline2

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!

Like

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