## Numerical solutions with CSpline

The CSpline function presented in a previous post  fits a series of cubic polynomial curves to a specified series of points, returning the Y values for listed intermediate X values.  I was recently asked if this could be reversed to find the X value for a specified Y value.  Since CSpline can also return the coefficients defining each of the cubic curves it turns out that this is fairly simple.  The procedure is:

1. Use CSpline to find the coefficients defining each segment of the spline, that is find the value of a, b, c, and d in Y = aX^3 + bX^2 + cX + d.
2. For each Y value:
1. For each segment
1. Solve the applicable cubic equation for the specified Y value
2. Check if any solutions lie within the X range of the current segment
3. If a solution is found add, it to the results array and go to the next Y
4. If all solutions are outside the segment X range go to the next segment
2. If no solution is found for any segment return a message to the results array and go to the next Y
3. Assign the results array to the function return value.

The new version of CSpline.xls, including the new function, SolveSplineA, can be downloaded from: CSpline2.zip

Some screenshots of examples are shown below:

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

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