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:

Some screenshots of examples are shown below:

Solvespline input and output (click for full size view)

Example 1

Examples 2 and 3

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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s