| OwenDuffy.net |
|
Several of my published Excel spreadsheets use cubic spline interpolations. This article explains what the functions do, and how to use them or modify their use.
A cubic spline interpolation is a method of interpolation based on a set of discrete x,y observations that depends on piecewise fitting of a polynomial to the data. The interpolation is much better than linear interpolation between adjacent observations (for the same number of samples), and simpler than fitting a high order polynomial to the full data set yet giving very good accuracy on phenomena with adequate samples.
The module CubicSpline contains four functions:
Functions csinterp1 will perform a cubic spline interpolation of a single abscissa (x value) given a set of x,y pairs as a column of x values and a column of y values. For example, =csinterp1(A5:A10,B5:B10,N8) will return the interpolated the y value for x at cell N8 based on the six x,y sample pairs at A5:A10 and C5:C10. Note that the columns of x and y do not need to be adjacent, but x and y must each be a range of 1 column by r rows, r must be equal, and r must be at least three.
csinterp1 is fine for a small number of interpolations based on a small sample set, but its performance (speed) will degrade as problems grow in size. For larger problems, cstable and csinterpolate will provide better performance.
Function cstable returns an array which is an intermediate set of data used for calculation of interpolations. To enter the function, you must select a range of three columns by the same number of rows as in the sample data, enter the formula and hit Control-Shift-Enter. For example, to create a CSTABLE using the same sample data as above (six samples), use the mouse to select an area of six rows by three columns and enter =cstable(A5:A10,B5:B10) and hit Control-Shift-Enter. See Excel’s help of using array formulas for more help.
If you want to change an existing CSTABLE to use more or less samples, you must change the area containing the function’s returned array. The simplest way is to delete the entire CSTABLE and reenter it with the new size.
Function csinterpolate will return an interpolated value based on the intermediate CSTABLE for a particular sample data set. For example, to calculate a value for x in cell A18 using a CSTABLE located at D5:F10, you would enter =csinterpolate($D$5:$F$10,A18). In this example, I also fixed the cell references to the CSTABLE so that I can copy the formula down the sheet for additional x values in the A column.
Note that in the example shown in the figure above, judicious choice of a small number of samples in the region where the valve characteristic is known to change rapidly gives a good characterisation of the valve.
These functions will also extrapolate, ie return values for an abcissa outside the range of the sample set, but beware of the accuracy issues with extrapolation.
Function CubicSpline_version can be called with argument “c”,”v”,”d”, and “u” to return module version information.
The code is copyright Owen Duffy 2008, all rights reserved. The module is protected and digitally signed to reduce the chance that it is distributed with changes that compromise its performance. You may not use the module for commercial purposes. No responsibility is accepted for your use of the module, you must verify results independently.
Having just installed Microsoft Office 2010, I find that Microsoft has worked very hard to prevent users enabling macros. Not only have they made it difficult to enable macros, but they issue dire warnings, dire enought to frighten most people off. Macros are a security issue, but disabling them is a bit like disconnecting from the Internet because it is risky. There are ways to work that make the risk manageable, if you don't know how to do that, don't use the tools with macros in them.
Next, Excel issues warnings that some user defined functions created in older versions of Excel conflict with internal functions in the newer version. In ALL cases I have observed, the "user defined functions created in older versions" are in fact functions supplied by Micorosoft in add-ins, usually the Analysis Toolpak. Many of my spreadsheets use the complex math functions from the Analysis Toolpak, and those functions are now included in the standard function library. Warnings in respect of those functions can safely be ignored, Microsoft is just creating alarm for no purpose.
In summary, the Cubic Spline interpolation functions in my spreadsheets work fine in Office 2010 when you get over Microsoft's hysteria.
© Copyright: Owen Duffy 1995, 2021. All rights reserved. Disclaimer.