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

Automating chart scale limits

Posted on 28/04/2010 by dougaj4

Edit 22 Mar 2014:  Also see https://newtonexcelbach.wordpress.com/2012/11/25/automating-chart-scale-limits-update/
for improved version with download link, and example of plotting a chart from a formula entered as text.

One of the more annoying things about Excel charts is that if you want to over-ride the automatic scale limits there is no built-in way to link the limits to a spreadsheet cell, so you have to go into the dialog box and change the numbers manually every time you want to change the scale.

Tushar Mehta recently posted a solution at Daily Dose of Excel, with an add-in providing this functionality, as well as other format chart adjustments.

In the following discussion John Walkenbach linked to an earlier solution that those who like to keep things simple may prefer.  This is a user defined function (UDF), that returns no data, but modifies the y-axis limits of any named chart.  It should be noted that this solution only works in Excel 2007 and later, is undocumented, and is not supposed to work at all, so use with caution.  I have taken the liberty of modifying John’s code so that the limits of both the X and Y axes can be linked to cell values, the axes limits can be re-set to automatic, and the status of each axis is returned by the function.  The revised code and a screen shot are given below.

Function ChangeChartAxisScale(CName As String, Optional Xlower As Double = 0, Optional Xupper As Double = 0, _
Optional Ylower As Double = 0, Optional YUpper As Double = 0) As Variant
Dim Rtn As String
'   Excel 2007 only

With ActiveSheet.Shapes(CName).Chart.Axes(1)
If Xlower = 0 Then
.MinimumScaleIsAuto = True
Rtn = "Xmin = auto"
Else:
.MinimumScale = Xlower
Rtn = "Xmin = " & Xlower
End If

If Xupper = 0 Or (Xupper < Xlower) Then
.MaximumScaleIsAuto = True
Rtn = Rtn & "; Xmax = auto"
Else
.MaximumScale = Xupper
Rtn = Rtn & "; Xmax = " & Xupper
End If
End With

With ActiveSheet.Shapes(CName).Chart.Axes(2)
If Ylower = 0 Then
.MinimumScaleIsAuto = True
Rtn = Rtn & "; Ymin = auto"
Else
.MinimumScale = Ylower
Rtn = Rtn & "; Ymin = " & Ylower
End If

If YUpper = 0 Or (Xupper < Xlower) Then
.MaximumScaleIsAuto = True
Rtn = Rtn & "; Ymax = auto"
Else
.MaximumScale = YUpper
Rtn = Rtn & "; Ymax = " & YUpper
End If
End With

ChangeChartAxisScale = Rtn
End Function

ChangeChartAxisScale Function, Click for full size view

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