Function for smooth curve on a chart
 

Knowing how the "smoothed line" charting option is created is useful for a
variety of estimation and presentation purposes. Brian Murphy has previously
identified a curve that matched but the code was given in Bezier coordinates
and not particularly transparent. It's actually fairly straightforward to set
up a spreadsheet to do this as described below, but it would be great to have
a simple VBA function if anyone has some spare time to write some easier code
for this?

In fact the curve matches a Catmull Rom spline, these are frequently used in
computer graphics and there are many references online. Given four points
A,B,C and D, the tangent vectors within the interval BC are set to half of AC
or BD with a limit on the lengths equal to 1.5 times the interval BC.
Repeating this procedure for each consecutive set of data points determines
all the points along the curve. At the endpoints the tangents just point
along the interval and can be set by using an extra point one interval length
away.

eg Consider this set of points:

x y
1 5
3 9
4 9
9 1
12 6

First add the extra points (-1,1) and (15,11) so that the same method can be
used to find all intervals. Taking the interval between (4,9) and (9,1) as an
example, the curve is given by

x(t): =SUM(t^{3,2,1,0}*M*{3;4;9;12})
y(t): =SUM(t^{3,2,1,0}*M*{9;9;1;6})

where t ranges between 0 and 1 and M is the matrix:

M = {-1,2,-1,0;3,-5,0,2;-3,4,1,0;1,-1,0,0}/2

The tangents are (3,4)/2 and (6,8)/2 which are both less than 1.5 times the
interval length: 1.5|(5,8)|.

This tangent condition holds everywhere except between (3,9) and (4,9) where
the longer tangent is L=5 times the interval length. An adjusted (cardinal)
matrix M' is used here instead:

M' = M - r{-1,2,-1,0;-1,1,0,0;1,-2,1,0;1,-1,0,0}

where r=0.5-0.75/L=0.35 in this case. In extreme cases there may be an
additional tweak that is used internally but by setting this r value
appropriately every test scenario could be matched to within the nearest
pixel even at maximum zoom.