Nonlinear regression in Excel tutorial

This tutorial explains how to set up and interpret a nonlinear regression in Excel with XLSTAT. Nonlinear regression is used to model complex phenomena which cannot be handled by the linear model.

Goal of this tutorial

Our goal is to study the relationship between the substrate concentration of an enzyme and its maximum velocity in two different groups. For this purpose we will use the Michaelis-Menten model.

Setting up a nonlinear regression

After opening XLSTAT, select the XLSTAT / Modeling data / Nonlinear regression command.

The nonlinear regression dialog box pops up. Select the data on the Excel sheet.

The Dependent variable (or variable to model, or response variable) is in our case the "Speed".

The quantitative explanatory variable is the concentration of substrate: "conc". In this tutorial, we want to explain the variability of the "Speed" by that of the concentration of substrate: "conc".

The group variable is used to separate the data in two groups "a" and "b". As we have selected the column titles, we have left the option Variable labels activated.

In the Functions tab, XLSTAT offers a wide choice of predefined functions whose derivatives are directly taken into account. Here, select Fit a single model from the drop-down list and then choose the Michaelis-Menten function.

NB: XLSTAT also leaves the choice to the user to enter a function defined by himself. The user will then have the choice to enter his own derivatives, or to let them be estimated by XLSTAT.

The computations begin once you have clicked on the OK button. The results will then be displayed.

Interpreting the results of a nonlinear regression

The first table of results provides simple statistics on the selected data. The second table (below) gives the model fit coefficients, including the RMSE (root mean square of the error) which gives an idea of the quality of a model. A model that fits the data better than another will have a lower RMSE. The sum of squares of residuals (SSE) is the criterion used by XLSTAT to fit the model.

In our case, the RMSE is 42.865 in the first group, and 86.893 in the second, which shows that the variability of the speed is better explained in the first group.

The next table provides details on the model parameters after adjustment for each group. We see that the pr1 parameters that correspond to the maximum speed of group "a" and group "b" are quite close.

The equations of the model are displayed and can easily be reused in Excel.

The next table (see Excel sheet) shows the analysis of residuals. One can notice that the model is well fitted on the first two observations of each group.

The first graph (see below) displays the data and curves of the fitted models, and confirms that the maximum speed of each group is close. The other graphs allow you to analyze the residuals, and are particularly useful when the amount of data is important.

As we have seen before, the maximum speed of the two groups is very close. You can therefore share this setting to get an overall fit value for it. To do this, restart an analysis.

In the Options tab check shared settings and click OK.

A new dialog box pops up, in which you choose to share the parameter "pr1", corresponding to the maximum speed.

You can now see in the model parameters table that the "pr1" parameter has the same value for both groups.

This allows us to have a model with an identical overall speed.

In conclusion, in the context of this analysis and of the selected model, the concentration of substrate allows to explain its speed very efficiently.