Nonlin97: A NEW EXCEL® VBA MACRO TO PERFORM NONLINEAR REGRESSION ANALYSES
Department of Agro-environmental Sciences - University of Perugia
Borgo XX Giugno 74 - 06121 Perugia - ITALY
| DOWNLOAD |
Reference To be added Version 1.2 (Update 01/02/2011) |
 Home Page |
1. General introduction
Nonlinear regression analysis has been recognised as a very handy tool to analyse agricultural experiments, involving plant-crop competition, crop growth, herbicide bioassays. Almost all the statistical aspects of non-linear regression analysis have been reviewed and reconsidered after the massive introduction of personal computers and this technique has considerably spread in the past few years, especially in weed science.
However, several authors insist on analysing experiments based on quantitative predictors by using polynomial regression or even by multiple comparison tests. Likely, a further spreading of non-linear regression techniques is restrained by the fact that calculations, though made easier by commercially available statistical packages, still require a certain degree of statistical expertise.
The aim of this work was to develop an EXCEL® VBA macro, which would enable users with a limited background in statistics to automatically perform nonlinear regression analysis, directly inside the most common spreadsheet.
Nonlin97 is included on the files Nonlin97.XLS, which can be downloaded free of charge from the above link. The macro is licensed "as is" without warranty of any kind. The entire risk related to the quality and performance of the macro is with the user. Nonlin97 can be freely used and distributed.
Usage should be acknowledged. Please, send comments to the author at e-mail: onofri@unipg.it
2. Instructions
Please, make sure that:- you have downloaded the latest version (see above);
- the SOLVER add-in is installed and enabled;
- macros are enabled in EXCEL;
- programmatic access to the Office VBA project is not denied.
Excel 2010
The add-in macro SOLVER.XLAM is included by default in Excel but kept disabled. In order to enable it, click the FILE Menu and choose OPTIONS. In the Excel Options dialogue box, select ADD-INS from the left sidebar and then hit the GO button, next to Manage Excel Add-ins at the bottom. Select the SOLVER ADD-IN option and hit OK to enable it.
Once you've enabled Solver, this will be available under the Data tab on the Data ribbon in Excel.
To enable macros in EXCEL 2010: click the FILE MENU and select Options from the left sidebar. Select TRUST CENTER and press TRUST CENTER SETTINGS. In Trust Center Settings, select MACRO SETTINGS from the left sidebar, choose DISABLE ALL MACROS WITH NOTIFICATION, select TRUST ACCESS TO THE VBA PROJECT OBJECT MODEL and hit the OK button. Save and close Excel completely, reopen Excel, open BIOASSAY97.XLS and you will see a SECURITY WARNING pop-up notification, that will let you enable macros in BIOASSAY97. You will have to do this every time, unless you selected the radio button for "Enable all macros" in the window above (do so only if you know what you are doing!).
To launch the programme, once you opened the file NONLIN97.XLS, click on the "NonLinear Regression" entry on the ribbon.
Excel 2007
The add-in macro SOLVER.XLAM is needed, and it must be installed from the original EXCEL® installation disks. To enable this add-in click the MICROSOFT OFFICE BUTTON, and then click EXCEL OPTIONS | ADD-INS. Select the solver, then select EXCEL ADD-INS from the Manage drop down and click 'GO...'. This will enable the Solver.
Once you've enabled Solver, Excel will auto-install the Add-in if it is not already installed, and then it will be available on the Data ribbon in Excel.
To enable macros in EXCEL: click the Office button in the upper left corner of the screen. Click the EXCEL OPTIONS button in the lower right. Click the TRUST CENTER button on the left. Then, at the bottom right, select TRUST CENTER SETTINGS. In the next window, select MACRO SETTINGS, then select the radio button for DISABLE ALL MACROS WITH NOTIFICATION. Check also the box next to TRUST ACCESS TO THE VBA PROJECT OBJECT MODEL. To close the Trust Center window, click the lower right OK button. Save and close Excel completely, reopen Excel, open BIOASSAY97XP.XLS and you will see a SECURITY WARNING notification beneath the Office ribbon. In the SECURITY WARNING banner, click the OPTIONS button and Select the radio box beside ENABLE THIS CONTENT then click OK. You will have to do this every time, unless you selected the radio button for "Enable all macros" in the window above.
To launch the programme, once you opened the file NONLIN97.XLS, click on the "Biologic assay" entry on the ribbon.
Excel 97 to 2003
The add-in macro SOLVER.XLA must be installed from the original EXCEL® installation disks. Make sure that this add-in is selected on the 'Tools/Add-ins' menu.
To enable macros in EXCEL, click in TOOLS to open the menu; then click on MACRO and second click on SECURITY (see below).
Now the following window will open:
Select the MEDIUM alternative. On the TRUSTED PUBLISHERS tab, click to select the TRUST ACCESS TO VISUAL BASIC PROJECT check box to turn on access. Now each time the program is launched an EXCEL opens, and the Security Warning window opens, click on ENABLE MACROS. You will have to do this every time, unless you selected the Security Level "Low" in the window above.
To launch the programme, launch the file NONLIN97.XLS, open the TOOLS menu and select BIOLOGIC ASSAY.
3. Troubleshooting
An Excel data sheet must be opened, before launching the macro. Otherwise you get an error message ('run-time error '91': Object variable or With block variable not set')
4. Setting the analysis
Experimental data have to be organised in a database, with observations in rows, and variables in columns. Basically, at least a dose column and a response column are needed, but a categoric variable might be required as well if several response curves have to be simoultaneously fitted.
The first row is reserved for variable names. See Figure 1 for an example on how a database is organised.
 |
| Figure 1. Example of database selection; note the first row of labels. |
At the beginning, the user is prompted to select the database (fig. 1). If a previous selection has been made on the same worksheet, that is proposed on the appropriate input-box.
Starting values of parameters are internally determined by Nonlin97; the user may provide a lambda value for the following Box and Cox (1964) family of transformations:
where W is the transformed variable, Y is the untransformed variable, l is the transformation parameter and is the geometric mean of the observations. By default l is set to 1 (no transformation); a value of 0,5 means that a square root transformation is performed, while a value of 0 means that a logarithmic transformation is performed. Often, a value of 0,25 has been found to be appropriate for dose-response analyses. A maximum likelihood value for lambda can be chosen by a direct comparison of RSS values obtained with different values of lambda (Box e Draper, 1987).
In order to obtain parameter estimates on the original scales, a Transform Both Sides technique has been adopted (according to Streibig et al., 1993).
The iterative procedure for parameter estimation is carried out by the EXCEL Solver. If convergence cannot be reached, a warning message is displayed and the analyses is stopped. Very often this depend on overparameterisation and/or wrong starting values for parameters. A more careful selection of model and/or starting parameter values should improve the iterative process.
When convergence is reached, results are displayed on a new worksheet, that is added to the current workbook. All the statistics are displayed, together with graphs to allow for appropriate graphical analyses of residuals to be performed.
5. Reference
BATES, D.M., WATTS, D.G., 1988. Nonlinear regression analysis & its applications., John Wiley & Sons, Inc, New York.
6. Aknowledgements
The author wish to thank Euro Pannacci (DSAA, University of Perugia) for testing the macro and providing useful comments and suggestions.