# Nonlinear regression in Excel tutorial

2019-05-27

#### Included in

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.

## Dataset for nonlinear regression

An Excel sheet with both the data and the results can be downloaded by clicking on the button below:

Data are fictitious and were created for this tutorial.

Using nonlinear regression, 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 is the concentration of substrate: "conc". We want here to explain the variability of the "Speed" by that of the concentration of substrate: "conc".

The group variable is used to separate the data for each of groups "a" and "b". As we selected the column titles, we 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.

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 gives basic statistics for the dependent and explanatory variables. The second table (see below) displays the goodness of fit coefficients, including the R² (coefficient of determination), and the SSE (sum of square of errors), the later being the criterion used for the model optimization. The R² corresponds to the % of the variability of the dependent variable (the dry weight) that is explained by the explanatory variable (the time). The closer to 1 the R² is, the better the fit.

In our case, 99% of the variability of the Speed is explained by the concentration of substrate in the first group, and 95% in the second, which is an excellent result.

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 number 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 parameter table that the "pr1" parameter have the same value for both groups.

This allows us to have a model with overall speed between groups, with a R² of 97%.

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

#### Contact our technical support team: support@xlstat.com

https://cdn.desk.com/
false
desk