Cubic splines are used to fit a smooth curve to a series of points with a piecewise series of cubic polynomial curves. In addition to their use in interpolation, they are of particular interest to engineers because the spline is defined as the shape that a thin flexible beam (of constant flexural stiffness) would take up if it was constrained to pass through the defined points. This post will present an Excel User Defined Function (UDF) to generate a “natural” cubic spline for any series of 3 or more points. Later posts will look at alternative spline formulations, and applications of the cubic spline to structural analysis.
A cubic spline is defined as the curve that for any two adjacent internal points:
- The curve passes exactly through both points
- The slope of the curve at the end points is equal to the slope of the adjacent segments
- The curvature of the curve at the end points is equal to the curvature of the adjacent segments
Alternative provisions for the end segments will generate different spline curves over the full extent of the curve. The most common provision for the ends is that the curvature is zero at both ends. This is known as a “natural cubic spline”. In a structural analysis context this corresponds to a beam that is free to rotate at both ends, but is constrained in position at the ends and a number of internal points.
Further details of the theory of cubic splines, and an algorithm for generating natural cubic splines are given in this Wikipedia article.
An excel spreadsheet with a UDF for generating cubic splines, based on the algorithm in the Wikipedia article, can be downloaded from: CSpline2.zip
The download is open source, and full VBA code for the UDF is freely accessible.
Example screen shots from this file are shown below:
Csplinea Function
Example 1; Fit spline to 5 data points
Example 1; Fit spline to 5 data points
Example 2; Fit spline to 9 data points on a circular arc
Example 2; Fit spline to 9 data points on a circular arc
“Dummy” data points at each end allow the curvature at the start and end points to be adjusted to the required value.
Example 2; Fit spline to 9 data points on a circular arc
Example 3; Fit spline to the deflected shape of a 3 span beam
Example 3; Fit spline to the deflected shape of a 3 span beam
Polynomial coefficients from example 3
Example 3; Bending Moments
Bending moments are calculated by multiplying the curvature at each point by the beam flexural stiffness, EI.
Newton Excel Bach, not (just) an Excel Blog 
Thank you for this spreadsheet/macro. Incredibly useful and very quick!
LikeLike
Thanks for providing this. I am going to be trying it out.
LikeLike
Pingback: Continuous beam analysis with cubic splines « Newton Excel Bach, not (just) an Excel Blog
Pingback: Calcolare lo spread emittente - Pagina 2 - I Forum di Investireoggi
Pingback: 2010 in review | Newton Excel Bach, not (just) an Excel Blog
can someone explain me how to use it??
LikeLike
Peter – I have just posted something about how to use array formulas with the CSplineA function as an example.
https://newtonexcelbach.wordpress.com/2011/05/10/using-array-formulas/
If you still have any questions after reading that, could you be more specific about what your problems are.
LikeLike
answer?
LikeLike
question?
LikeLike
Pingback: Daily Download 22: Splines and Curves | Newton Excel Bach, not (just) an Excel Blog
Hi, Thank you for your demo and information about this spline method. I have convert this csplineA excel file to C#. Please check at my site 🙂
Thank you
LikeLike
“Cubic Splines | Newton Excel Bach, not (just) an Excel
Blog” ended up being a superb blog post, can not wait to browse far more of ur postings.
Time to waste numerous time on the internet haha.
Thanks a lot ,Preston
LikeLike
Pingback: useful links | IL SUPEREROE
This looks like a very useful function, but when I open it in Excel:Mac 2008 there is an error in the spline results. Somewhere along the line, Excel thinks something is text when it should be a number (#NAME?). Any idea on how to solve this problem for Macs? Thanks for your work on this, hope I can use it. Cheers
LikeLike
Unfortunately Excel for Mac 2008 does not have VBA, so no User Defined Function will work in that version. I believe that the latest Excel for Mac does have VBA restored, so UDFs should work if you update, but I can’t guarantee it as I don’t have a Mac available for testing.
LikeLike
Ah, figured as much. Thanks for the quick response. I’ll let you know if I find a workaround.
LikeLike
This is a great macro, thanks very much for sharing. I’m fairly new to spline interpolation so apologies if my question is obvious but is this a basis-spline? I read that a basis-spline would only work on ascending values of x, order, but this macro works for non-ascending values of x. However, if this isn’t a basis-spline could you please briefly explain what type of spline you would categorise it as? Thank you!
LikeLike
The x values do need to be in ascending order! The function will return a result with non-ascending x values (as long as no two adjacent values are equal), but the resulting curve makes no sense.
For a curve where the x values may not be ascending the most common option used is a Bezier curve. There may be others, but I haven’t looked into it.
LikeLike
Thanks for posting this very useful code. One nit: you might mention that the interpolation x-values (Xint in the VBA code) needs to have at least three values in its range to get proper results. I tried it for a single-cell Xint, which caused Xint to be passed in as a double. This flagged an error on the call to UBound(Xint) since that function works only on arrays. To get around that, I inserted:
If Not Typename(Xint) Like “*()” Then
ReDim Xint(1, 1)
End If
CSplineA returned results after this change, but then I found that calls with single cells for Xint returned grossly incorrect values. When I changed Xint to also include the XVal entries that bracket my desired interpolation point, however, the problem was fixed.
LikeLike
Jim your code will create an empty array called Xint. I have modified the code to create an array XintTemp(1,1), copy Xint into that, then copy XintTemp into Xint. Also the value nint needs to be set to 1.
I have only done quick testing, but it seems to be working OK.
Download from:
http://interactiveds.com.au/software/CSpline2.zip
LikeLike
Thanks for the quick reply.
LikeLike
Thanks Jim, I’ll have a look at that.
LikeLike
Hi sir. Currently I’m working on a project titled interpolation of planar curve with different parameterization and my supervisor told me to create a smooth B-spline curve using Microsoft Excel. I am having difficulties with the task given and may i know if the example you have given above could be used to create the b-spline? Do you have any example for the B-spline Curve? Thank you in advance.
LikeLike
The simplest way to create a a smooth B-spline in Excel is to create an XY (scatter) chart from a set of points, and select the smoothed line option to connect the points. The resulting curve is an example of a B-spline.
The cubic splines described here are also B-splines, so you could use example from here as well.
LikeLike
Thanks for the reply sir. Now, after doing the B-spline Basis function calculations for zeroth and 1st degree, how am i supposed to link the calculation to create a B-spline curve with control points using Excel and Could you please enlighten me on the B-spline basis function calculations for 2nd degree and onwards ? I’m sorry for troubling you sir because my supervisor not providing me enough informations. Thanks in advance.
LikeLike
Pingback: Excel相关 | 优的水杯
Thanks for sharing, you made my day!
LikeLike
Is there a spline that can be used to interpolate using polar coordinates? I have two irregular (roundish) closed shapes that I want to work out the best way to fit them together, i.e. rotate one relative to the other to give the least amount of difference at the seam where they touch.
In addition to rotating, I’d like to be able to change the origin of one shape w.r.t. the other, then use the spline interpolation to calculate the new coordinates to make the comparison. Any advice on how to do the spline in polar coordinates would be appreciated. Thanks in advance.
LikeLiked by 1 person
Andrew – The latest version of the CSpline spreadsheet has Cardinal Spline function that should do what you want.
Download from: http://interactiveds.com.au/software/CSpline2.zip
I have also edited the link in the post to download the current version.
LikeLike
Doug, I’ve had a look at the card spline, and I must admit that I am a little bit uncertain as to what the three parameters are…
If I have polar coordinates after I transform my origin position, how do I now interpolate my new polar coordinates, i.e. after my translation of the origin, if my original point was 0 degrees, 10 radius, 10 degrees 10 radius etc, and is now -1 degrees 9.8 radius and 8 degrees 10.1 radius, how do I calculate my new 0 degrees and 10 degrees radius values?
Sorry for the n00b question.
Regards
Andrew
LikeLiked by 1 person
Hi. I have followed your question with some interest. Send me an email
alfred.vachris@gmail.com . I would like to propose an alternate solution.
Would love to see a sketch of your two curves.
Best Regards
Alfred Vachris Excel VBA Developer
LikeLike
Andrew – it’s not an easy question, a search on “cubic splines polar coordinates” comes up with quite a few hits but they are all pretty heavy on the maths, and I don’t have time to work my way through them at the moment.
With the cardinal spline in the CSpline2 spreadsheet the XY values are the usual Cartesian coordinates, and the L value is a measure of the distance measured along the curve. The distance between each point defining the spline is given a value of 1, so an L value of 1.5 defines a point half way along the segment from point 2 to point 3. It seems that the output starts at L=1, i.e. from point 2 onwards (it is some time since I wrote the code, and I don’t remember the reason for that).
Some quick tests with the CardSpline function suggest that it doesn’t work well with a closed loop. You might do better with the xl_PSplinep function in xl_Spline-Matrix2. To use that you will need to download and install Python and the Alglib library as described in the link below (both are free):(https://newtonexcelbach.wordpress.com/2014/09/12/excelpython2-alglib-and-spline-matrix-update/).
To use this one it seems to work best if you convert the points defining your curves to XY coordinates, generate XY coordinates for the complete loop at fairly close spacing, and then convert these back to polar. Finally you could use an ordinary cubic spline to interpolate between the polar coordinates, to get the radius for any given angle. Note that in this function a value between 0 and 1 defines a point along the complete curve.
If you get a working system out of that (or find a simpler way), I would be interested to hear.
Don’t hesitate to ask further questions.
LikeLiked by 1 person
Hi Doug – it shouldn’t be hard to modify the spline end conditions for a closed curve by continuing the cubic formula around the loop.
In the case of a cardinal spline connecting ABCD, just apply the same algorithm to each of the four middle segments of DABC,ABCD,BCDA,CDAB. (i believe a similar approach is used for smooth scaling of fonts when zooming in on text on a touchscreen.)
For a curve with second order continuity the end conditions can be modified as in: http://mathworld.wolfram.com/CubicSpline.html [eqn (19)]
Numerous references suggest other choices of basis functions may be better suited to angular data (RBF, trigonometric, wavelets, Euler spiral, …) but i haven’t any experience of these in practice. The scipy python library has some options for generating closed curves that may be worth exploring too.
LikeLiked by 1 person
Thanks for your comments Lori and Alfred. I’ll be travelling (Italy and UK) for the next four weeks, so I won’t have much time to contribute, but I will follow any developments with interest.
LikeLike
Super helpful tool. Thank you!
LikeLike
Thank you for providing this tool. I am finding it useful for fitting diffusion data to determine concentration-dependent interdiffusion coefficients.
If I had a dataset with a larger number of XY values, could a function be devised to perform a ‘best fit’ cubic spline scheme featuring a user-prescribed or algorithmically-determined number of splines where the function would pick the optimal endpoints for each spline automatically? I’d hate to work with 100 polynomials for 101 XY points if there is little added benefit as compared to, say, 5 polynomials if the endpoints were well-chosen.
LikeLike
David – yes, there are a few options:
Probably the simplest is the AL_Spline-Matrix spreadsheet from:
https://newtonexcelbach.wordpress.com/2010/06/07/alglib-spline-functions/
This uses a VBA version of the Alglib maths library, which includes spline fitting functions that allow you to specify the number of splines. The download file includes all the necessary routines, so it should run without installing anything else. The only drawback is that the VBA version of Alglib is no longer being updated.
I also have a version that uses the Alglib Python interface, which you can download from:
https://newtonexcelbach.wordpress.com/2014/09/12/excelpython2-alglib-and-spline-matrix-update/
For this one you need Python, Alglib Python version, and ExcelPython. You need to install Python first, then Alglib. The required ExcelPython files are included in the download. See the link for more details.
Finally there is a spreadsheet that links to the Python Scipy package, via ExcelPython. If you install a version of Python that includes Scipy and Numpy (such as Anaconda), then the remaining required files are included in the download from:
https://newtonexcelbach.wordpress.com/2014/12/14/numpy-and-scipy-for-excel/
The best function for your use is probably xl_UniSpline on the Interp sheet, which has an optional K argument, specifying the degree of the spline, and S (between 0 and 1) which controls the degree of smoothing, between none at S = 0 to 1 spline at S = 1. The documentation on the spreadsheet is very sparse, but Scipy comes with a detailed (but not particularly friendly) manual.
Please ask if anything isn’t clear, and let me know if you have any problems getting anything to work.
LikeLike
I am neither au fait enough with the maths nor with the coding for the cspline UDF, but I was delighted a year ago now when I found it browsing the web. I have used it extensively to help me in my online hobby of virtual sailboat racing. Thank you!
Using it also made me teach myself the basics of Excel array algebra, and, because both its source data and results must be set up in columns, a bit more than the basics with respect to indirect addressing (to auto-invert rows of data into columns).
Much more recently I found much the same in terms of functionality and underlying maths from SRS1; their data curve fitter. It is a little easier to use in that it doesn’t need array definitions and is indifferent as to whether the source data is presented in columns or rows. Problem is of course 1/ it is only free to use for a while and 2/ it invents extraordinary tails for points that fall outside the range of the source data.
My question then: are there any plans to make cspline work with data in rows as well as with data in columns?
LikeLiked by 1 person
Jan – I’m glad you find it useful.
See
https://newtonexcelbach.wordpress.com/2014/02/16/cubic-splines-with-horizontal-data/
for the latest version that accepts input data in rows. It also has an optional Transpose arguments, so you can have input data in rows with output in columns, or vice versa.
LikeLike
I just found your spline worksheet! Does exactly what I want it to do – quick analysis in a visual way to set up my algorithms for more in-depth work in Igor. I saved it as an add-in and added it in to Excel so the functions are available everywhere.
LikeLike
Thanks for the feedback Matthew. It’s good to know people are using my work, and developing it to suit their needs and method of work.
You might like to search the blog for Python links as well, particularly links to Numpy and Scipy.
LikeLike
Hi Doug, thank you for the template! It is very handy!
We are using the file “CSpline2.xls” to filter Heart rate variability. We are inputing our data between A36:A40 and B36:B40 under “CSpline” tab.
What’s the difference between the “spline 1,2,3,4” in D34,E34,F34,G34?
And also, do you have any recommendation as to which template use for values like HRV? This data is given as X: time(seconds), Y:interval (miliseconds)
Thank you
LikeLiked by 1 person
Patric – The four different curves are generated by different end conditions. You can specify either the gradient or the curvature at each end. See:
https://newtonexcelbach.wordpress.com/2009/07/12/more-on-cubic-splines/
for more details. Also see the notes at the top of the page for a list of the input options, and open the function with the function input dialog to see the name of each input argument.
You might also like to have a look at:
https://newtonexcelbach.wordpress.com/using-array-functions-and-udfs-and-following-links/
for suggestions on using UDFs and array functions. You can use the spreadsheets as they are as a template, but you can also use the functions to set up the sheet to suit your own requirements. For instance you could delete everything from Row 60 downwards on the CSpline sheet, delete Spline2, 3 and 4, and set up Spline1 with your preferred end conditions. Alternatively you could leave the four splines so you can play with the end conditions to see which gives the best results. Also you can extend the range of X values and re-enter the array function to cover the extended list of values.
As for which is the most appropriate spline, that really needs a knowledge of the phenomenon you are investigating. If the X values are always increasing, and the Y values may increase or decrease, then the functions on the CSpline sheet would be appropriate, but the most appropriate end conditions would need some investigation. The default values (zero curvature at the end points) would be a good starting point though (Spline1).
LikeLike
Hi, Doug,
thanks for the extension. I’ve been using XlXtrFun for years, but it’s ten years since it’s last been updated, so I’d like to switch to your tool whose last update is much more recent (April this year if I’m correct). It’s a pity that’s not an add-in – I’m not too familiar with VBA, but I can manage to a small extent. I see that in the xls file there are two VBA modules, and many functions. I’m mostly interested in cubic spline interpolation and piecewise linear interpolation. Which is the minimum set of functions I should include in my workbook? I know I could just copy both modules, but there are a lot of function I’ll never use (such as CardSpline), so I was wondering what’s the minimal working set. Thanks in advance
LikeLike
PS I’m using Excel 2010, but I guess that doesn’t really matter for my question, right?
LikeLike
Probably the easiest thing to do is to save the spreadsheet as xlam (add-in format) and you can then use it like any other add-in. If you want to do that you could first delete all the sheets except “about” as you can’t access them from an add-in anyway.
If you want to add the vba to an existing file the easiest way is to open both files, then in the VB Editor Project Explorer window you can just drag and drop the modules you want from CSpline2 to your new workbook.
There are 3 modules in the latest version (1.13). You need mCSpline and mGetArray, and the functions in mResizeArray are very useful, so I would recommend keeping that.
You could work through mGetArray and delete the functions you don’t use, but it won’t save much memory, and I would suggest leaving them.
The spreadsheet was written in Excel 2010 or earlier, so the version should be no problem.
Any questions, please ask.
LikeLike
This is an amazing contribution! Thank you for sharing.
I have a quick question, have you developed a smoothing spline version?
LikeLiked by 1 person
Thanks for the comments.
The links below have functions for fitting smoothed splines to a data set. They both need Python and ExcelPython, but both are free and all the ExcelPython files are included in the downloads. The second also needs Alglib, which also has a free version.
https://newtonexcelbach.wordpress.com/2014/09/12/excelpython2-alglib-and-spline-matrix-update/
https://newtonexcelbach.wordpress.com/2014/07/23/alglibpython-linear-and-non-linear-fitting-functions/
LikeLike
Hi – this is a great post and really useful. I’m wondering if you can help me as I’m losing my mind a little trying to apply this to a flow series.
I can see how this works on a stock series and applying it is very straightforward. I can’t see how I can get it to apply to a flow series. My aim is to basically break down a quarterly series into monthly data points. So for example, to be able to break down a data point for quarter 1 of a certain year and split it between its three component months (in this case, Jan, Feb and March).
Am I missing a really obvious trick?
LikeLike
Sorry for the late reply. I must have been busy when you posted it.
I am not sure what you mean by “stock series” and “flow series”, but if you have your X data as date numbers (rather than text), and you have values for 1-Jan, 1-Apr, 1-Jul …, then you should be able to interpolate for any other date, so you could set up a table for 1-Jan, 1-Feb, 1-Mar, … as long as these are also date numbers.
Does that answer your question?
LikeLike
Hello,
I have two columns of 10 values and I’m just trying to get CSplineA to interpolate a y value for a given x value.
Y_Data X_Data Y_interp
0.4233 0.8374 0.4233
0.4298 0.7792 0.4298
0.4363 0.7258 0.4364
0.4427 0.6767 0.4435
0.4491 0.6315 0.4514
0.4554 0.5898 0.4601
0.4616 0.5514 0.4696
0.4676 0.5158 0.4799
0.4735 0.4829 0.4907
0.4791 0.4525 0.5021
My command is simply = CSplineA(col(X),col(Y),X_Data). Not entered as an array formula.
Unfortunately, the spline routine doesn’t return interpolated Y values similar to the parent data for X values less than 0.73.
Any suggestions?
LikeLike
Jon – If you sort the data in order of ascending X it should give correct results.
LikeLike
Thank you for the reply;
Is there anyway I can modify the VBA code to handle both ascending or descending data? For my purposes, the dependence on the direction of the X array is very problematic.
LikeLike
Jon – There are several options. In order of increasing difficulty:
– Wait for me to add a sort option to the code. I will try and do this by next week, but it definitely won’t happen before the weekend.
– Have a look at the Alglib spline functions at: https://newtonexcelbach.wordpress.com/2014/08/02/alglibpython-spline-functions-update/
The spline functions sort the data by default. The only problem with this is that you will need to install the Alglib library, Python, and ExcelPython. If you are happy to do that this is probably the best option (and all the required downloads are free and useful)
– Try the Python spline functions at: https://newtonexcelbach.wordpress.com/2016/01/04/xlscipy-python-scipy-for-excel-update-with-new-functions/
You still need Python (including Scipy) and ExcelPython, and you will need to check the Scipy documentation to see how it handles X data in decreasing order.
– Write your own VBA code. It would probably be easiest to write a new function to reverse the order of the X and Y data, then call CSplineA and return the resulting values.
LikeLike
Thanks. I’ll take a shot at the code.
LikeLike
The download version of CSplineA will now work with x values sorted in either direction. I will post details and code in the next few days.
LikeLike
Jon – see:
https://newtonexcelbach.wordpress.com/2016/03/01/cubic-splines-with-descending-x/
LikeLike
Pingback: Linear Interpolation in Excel | EngineerExcel
I have some annual GDP data but i want to convert it into monthly GDP data. How do i go about it
LikeLike
If you have your dates entered as date numbers (rather than text) then you can use the cspline function to get an interpolated annual GDP for any specified date. You could then divide by 12 to get GDP/month.
LikeLike
Hey Dougaj4,
I’ve liked using your tool. But I thought I’d leave some feedback because it seems that it has some bugs in some cases.
I’m trying to fit an S-shape curve. I’m having some difficulty with it. Here are my findings.
The monotonic spline is not always monotonic. It can tend to oscillate.
The chartspline function doesn’t always follow a smooth curve plotted by excel.
There is a bug in the cardspline function, above a certain value (~1000) in my x-set it seems to suddenly depart completely from the function. It doesn’t make any sense.
Perhaps its because the x-values i’m using are not equally spaced? Is that a requirement here?
Anyways, I’m looking at the Alglib tools now, to try the catmull-rom spline.
LikeLike
Thanks for the feedback. I’ll try and find some time to have a closer look. You might also like to have a look at recent posts on linking Excel to Python. If you search the blog for Scipy you should find posts on a Scipy spreadsheet which includes several Python curve fitting functions.
Also, if you could send a spreadsheet with examples of the problems to my gmail account (dougaj4), that would be helpful.
LikeLike
Hi Doug,
I just stumbled upon your tool – great work! I’d like to have a natural cubic spline (Curvature = 0 at end points) where it continues linearly on the Slope at the two end points beyond the two end points. Using your function, I can easily find the Slope at the two end points and I could hack together my solution with that Slope value and a conditional – but is there a more elegant way to just have the spline continue on these slopes once past the two endpoints?
Thanks,
James
LikeLike
I don’t think there is a way to do it just using the spline function, so you would have to tack on a straight line interpolation at either end. The neatest way to do it would be to have a separate UDF with the same input arguments as CSplineA, including the required slope at either end. For x values outside the spline range it would do a simple straight line extrapolation, and for values inside the range it would call CSplineA, which could either use natural end conditions, or the specified slope.
You might also like to have a look at:
https://newtonexcelbach.wordpress.com/2017/02/05/weighted-least-squares-regression-using-excel-vba-alglib-and-python/
which has curve fitting routines from Scipy and ALGLIB linked to Excel through xlwings. They both have a variety of spline functions, and other curve fitting routines. You need to install Python and Scipy and xlwings, but that’s a simple process using Anaconda Python. For the ALGLIB version you also need to install ALGLIB, which has a free version.
LikeLike
Hi Doug,
Thank you for sharing this! I am trying to call the CSplineA function directly from VBA, as opposed to using it in a formula on a worksheet. Is this possible? If so, do you have an example?
Cheers,
Erik
LikeLike
Yes, the function can be called from VBA:
Function CSplineA(Xa As Variant, Ya As Variant, Xint As Variant, Optional Out As Long = 1, Optional EType As Long = 1, _
Optional End1 As Double = 0, Optional End2 As Double = 0, Optional TransposeH As Boolean = True)
The required inputs are variant arrays, so you can pass an array of doubles, or variants, or even a range object. The code requires a 2D column array with base 1, but it will convert a 1D array, or a range, to the required format.
So if you don’t need any of the optional arguments you can do:
Dim Xa() as Double, Ya() as Double, Xint() as Double, ResA as Variant
‘ Redim Xa, Ya, and Xint to the required size, and generate data
ResA = CSplineA(Xa, Ya, Xint)
LikeLike
Hi Doug,
Thanks for your prompt reply! It is probably something simple I am doing wrong…. At first I tried creating the arrays as:
Dim Xa as variant
Xa = array(1, 2, 3)
‘ etc…
but that gave an error in CSplineA, which converted the array (GetArray) to a single item.
I then tried:
Dim Xa as double
Redim Xa(3)
Xa(1) = 1
Xa(2) = 2
Xa(3) = 3
but that yielded a 458 error “Variable uses an automation type not supported in Visual Basic” right at the call to GetArray(). I am not sure what I am doing wrong here.
Is it possible I don’t have the current version of the VBA code? The file I have is version 1.16 according to the version history on the About tab. I would be glad to send you the sample calling routine if that helps.
Cheers,
Erik
LikeLike
Erik – try declaring the input arrays as a 2D column array, with a specified lower bound:
Redim Xa(1 to 3, 1 to 1)
If that doesn’t work let me know, and I’ll have a closer look.
LikeLike
Sorry, above should have read
Dim Xa() as Variant
and
Dim Xa() as Double
I was just typing from memory….
LikeLike
Hi Doug,
Thanks, that worked!!
Cheers,
Erik
LikeLike
Than you!
LikeLike
Hello Doug
Thanks for this very handy tool.
Is there a way to suppress the slope, curviture, etc. columns and just keep the spline column only?
Thank you
Amar
LikeLike
Yes, the short answer (which works in this spreadsheet and others with the required code):
1. Select the top-left cell of the output range down to the bottom of the first column (or whatever range you want to display).
2. Press Ctrl-shift-R
To return to displaying the full array select the top-left cell and press Ctrl-shift-S
For a longer answer, and procedures in spreadsheets that don’t have the VBA routines see:
Note that the link above needs to be updated to cover the latest Excel versions that by default return the full array, rather than just the top-left cell, but the VBA macros should work in all versions.
LikeLike