Your data analysis solution

Cubic spline in Excel tutorial

2016-05-06

This tutorial will help you set up and interpret a Cubic Spline in Excel using the XLSTAT software.
Not sure this is the modeling feature you are looking for? Check out this guide

The data that we use are the speed of numerous cars and the distance they need to stop [Ezekiel, M. (1930)]. Note that the data were recorded in the 1920s.

The goal is to fit those data using a cubic spline. 

Setting up a cubic spline in XLSTAT

After opening XLSTAT, select the XLSTAT / Modeling data/ Cubic splines command, or click on the matching button of the toolbar.

Once you've clicked on the button, the dialog box appears.

The data are presented in a table of 50 observations and 2 variables. Speed is the X variable and Distance the Y variable. As we select the column title for the variables, we leave the option Variable labels activated.

Cubic Spline data

In the Options tab, three approaches are proposed to defined the spline nodes. Here we choose to fix the number of nodes to 10.

Cubic spline options tab

The computations begin once you have clicked on OK. The results will then be displayed in a new sheet.

Interpreting the results

The first results being displayed are the statistics and the correlation matrix for the 2 variables. Next, we get the table of the coefficients of the polynomials for each interval.

Cubic spline coefficients

The cubic spline is then displayed on graph.

Cubic spline graphic results

Finally, table and graphs enable us to take a closer look at each of the residuals. 

1c26995d494fb3061dd0ae8571ffc0a4@xlstat.desk-mail.com
https://cdn.desk.com/
false
desk
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/portal/articles/autocomplete
9283