It was recently reported to me that the cubic spline spreadsheet CSpline2.xls (posted here) was crashing on loading in Excel 2003. It seems that the problem was with the ChartSpline function, and that there are no problems if this function is removed. I haven’t tracked down exactly what causes the crash, but until I do I have saved two versions of the file:
- CSpline2.xlsb includes the ChartSpline function for use in Excel 2007 and 2010
- CSpline2.xls has the ChartSpline function deleted
The new files can be downloaded from CSpline2.zip
Please let me know if there are any remaining problems.
Newton Excel Bach, not (just) an Excel Blog 
Apologies, I’m partly responsible for that, according to msdn kb Q213683 there can be problems in Excel 2003 and earlier with plotting udfs that are volatile and refer to chart data. An approximation for earlier versions is here: http://xlrotor.com/resources/files.shtml but it’s not always that close, for example: (9,10) (9.5,12) (10,12) (12,7). The reason for reading values from the chart directly rather than referring to worksheet ranges, is it simplifies the function input and is more general since it supports series that refer to arrays or named formulas.
On a related note, the curves created with drawing tools use essentially the same algorithm with different end conditions and the control points can be edited. I’ve been wanting to get round to writing a utility to reproduce graphical data in svg or kml format via charts or shapes. There would be many applications maybe someone might be interested to assist? Here’s a chart created from the wikipedia worldmap file using a single series: http://img263.imageshack.us/img263/5006/worldv.png
LikeLike
Hi Lori – thanks for the comments and the links. Regarding the named formulas, I must have a look at tieing this in with the Evaluate function (see also recent comment from Jeff Weir).
I’m interested in your svg/kml project. I don’t know anything about these formats (apart from what Wikipedia just told me), but it shouldn’t be too hard to convert either shapes or charts.
You might be interested in this link I foumd yesterday:
http://www.automateexcel.com/2005/07/28/google_maps_in_excel/
Haven’t had time for a proper look, but seems like it could be useful.
LikeLike
Pingback: Interpolation Update | Newton Excel Bach, not (just) an Excel Blog
Doug – I think your function is already flexible to deal with named formulas, arrays or multiple ranges as series values as it’s the evaluated array of chart values that is being passed in. A quirk is that chart array values can’t seem to be assigned to variables directly, a workaround is to wrap in the Array function. The option for multiple input values and the missing variable declaration are welcome additions.
Interesting link to google maps – my hope was to be able to translate arbitrary vector graphics to chart lines or shapes both of which allow for either straight or smooth paths. Since many wiki diagrams and maps are in this format, it would allow one to add data to and manipulate these graphics instead of starting from scratch. It should be just a matter of parsing the xml and mapping to Excel object properties but may be trickier to implement in practice.
LikeLike
alpha and beta need Slope not Y(i+1)-Y(i)
LikeLike
Can you provide more details? e.g. which alpha and beta are you referring to, in which function, and are you referring to the code or the documentation?
LikeLike