Cubic spline in Excel tutorial

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.

Dataset for running a Cubic Spline

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.

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

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.

The cubic spline is then displayed on graph.

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