Cubic Hermitic and Cardinal Splines

Posted on 14/05/2010 by dougaj4

In my previous post on splines I described a quick and dirty approach to adjusting a cubic spline to fit monotonic data, which did not meet with universal approval, so I have followed up Mike Woodhouse’s link: (http://en.wikipedia.org/wiki/Monotone_cubic_interpolation) which gives a more mathematically correct procedure.

I have added three new functions to the spreadsheet CSpline2.xls, including full open source code. 

The functions are: CHSplineA

CHSplineA Function (click for full size view)

CHSplineA fits a Cubic Hermitic Spline curve through a series of XY points, with the option to force monotonic output.  Typical input and output is shown below:

CHSplineA input and output

Output graphs from CHSplineA

The function CardSplineA fits a Cardinal Spline to a series of XY points. The code is based on a perl script posted on the Wikipedia article:

CardSplineA Function

 The Cardinal Spline allows functions with non-monotonic X values.  There are three options for the interpolation points; either An L value (specifying a segment number and position of the point along the segment length), or X or Y values.

CardSplineA output

CardSpline Graph

Finally I have adapted Lori Miller’s code to extract XY data from chart series, the main change being to convert it into an array function, which should improve performance:

ChartSplineA Function

ChartSplineA Output

Posted in Charts, Excel, Maths, UDFs, VBA | Tagged , , , , , , | 8 Comments

The Ballad of Erica Levine

Posted on 09/05/2010 by dougaj4

Two songs from Frankie Armstrong.  The first is the Ballad of Erica Levine by Bob Blue, and the second, I’m Gonna be an Engineer by Peggy Seeger.  Frankie was singing traditional folk songs in the UK in the 60’s and 70’s, just when I was listening, but somehow I missed her back then, and just discovered her work via You Tube yesterday.  The songs I have chosen are not typical of her output, but they seemed appropriate for Mother’s Day.

… and perhaps they are even more appropriate for the 50th anniversary of the contraceptive pill.

Posted in Bach | Tagged , , , | Leave a comment

Using Cubic Splines in Practice

Posted on 06/05/2010 by dougaj4

I recently had an interesting query from Georg,  a geography student in Germany regarding the use of cubic splines to fit a smooth curve to experimental data.  The graph he sent me is shown below:

The cubic spline has produced a nice smooth curve, but the problem is that it is supposed to be a cumulative frequency curve, and cumulative frequency curves are not supposed to slope downwards, let along pass below the zero line.  Unfortunately cubic splines don’t know that, and will just adopt the shape dictated bt the given points.

One solution is to insert additional points in the areas with problems as illustrated below:

Original data

Insert two additional points

 Which gives us the result we want!

The new all ascending curve

Posted in Charts, Excel, Maths, Newton, UDFs | Tagged , | 9 Comments

Numerical solutions with CSpline

Posted on 05/05/2010 by dougaj4

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:

Solvespline input and output (click for full size view)

Example 1

Examples 2 and 3

Posted in Excel, Maths, Newton, UDFs, VBA | Tagged , , , , | Leave a comment

More about not being different

Posted on 29/04/2010 by dougaj4

Last year I posted a link to a puzzle at Tanya Khovanova’s Math Blog.  I just came across a follow up to that puzzle:

The Odder One Out

where she discusses the responses. 

Which by some devious path reminded me of my very favourite bit of Monty Python’s Life of Brian:

Posted in Bach | Tagged , | Leave a comment