BIOASSAY97: A NEW EXCEL^® VBA MACRO TO PERFORM STATISTICAL ANALYSES ON PESTICIDE DOSE-RESPONSE DATA

Reference Onofri A., 2005. BIOASSAY97: a new EXCEL VBA macro to perform statistical analyses on herbicide dose-response data. Rivista Italiana di Agrometeorologia, 3, 40-45. Version 2.512 (Update 20/10/2008 - Added compatibility to Excel 2007)

Home Page

---

1. General introduction

Nonlinear regression analysis has been recognised as a very appropriate tool to analyse dose-response studies, involving pesticide antagonism, synergism, selectivity and resistance, as well as the effect of safeners, adjuvants and environmental side effects of pesticides. Advantages over the other techniques involving the linearisation of data (especially probit and logit analysis) are in that these latter methods have usually limited biological meaning and do not always allow for an appropriate description of data in certain response ranges, such as at extremely low and high doses.
Almost all the statistical aspects of non-linear regression analysis on bioassay data 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. A list of references has been given later on.
However, several authors insist on analysing dose-response data by linearisation 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. Indeed, non-linear regression routines found in those statistical packages have not been specifically developed to analyse bioassay data: certain routine tasks, such as the calculation of ED levels with confidence limits or the comparison among different dose-response curves, are not easily performed without a certain programming effort.
The aim of this work was to develop an EXCEL^® VBA macro, specifically thought to deal with dose-response curve analyses, which would enable users with a limited background in statistics to automatically perform log-logistic analyses, directly inside the most common spreadsheet.
All the rationale and theory behind this macro have been taken by Streibig et al. (1993 a and b) and all the tools have been included to carry out appropriate analyses on the main part of bioassays regarding the study of factors influencing pesticide performances.
BIOASSAY97 is included on the files BIOASSAY97XP.XLS (Excel 97 to 2003) and BIOASSAY97XP.XLSM (Excel 2007), that can be be freely used and distributed, but changes on the original code are not allowed. 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.

Usage should be acknowledged (Onofri A., 2005. BIOASSAY97: a new EXCEL VBA macro to perform statistical analyses on herbicide dose-response data. Rivista Italiana di Agrometeorologia, 3, 40-45). Please, send comments to the author at e-mail: onofri@unipg.it

---

2. Instructions

Excel 97 to 2003

Use the file BIOASSAY97.XLM.The add-in macro SOLVER.XLA is needed, and it must be installed from the original EXCEL^® installation disks. Make sure that this latter add-in is selected on the 'Tools/Add-ins' menu.

The file BIOASSAY97XP.XLS containing the macro BIOASSAY97 should be put on the same directory where SOLVER.XLA is found, i.e. C:\Program Files\Microsoft Office\OFFICE11\Library\SOLVER\ (it might also be C:\Program Files\Microsoft Office\OFFICE10\Library\SOLVER\ or similar, depending on the language and on the Office version you are using). After done this, you might create a shortcut on your desktop to lunch the macro more easily.

Please, make sure you have downloaded the last version (at least 2.512, update october 2008) that solves a bug with the English Version of Excel and a bug with initial parameter guesses.

Please, make also sure that macros are enabled 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. 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 BIOSSAY97XP.XLS or click on your desktop icon (if you manually created one shortcut) and EXCEL is started. Then clicking on the EXCEL menu bar to open the "Tools" menu, the user can notice that a new entry has been added under this menu: "Biologic assay".

Excel 2007

Use the file BIOASSAY97.XLSM. The add-in macro SOLVER.XLA 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.

The file BIOASSAY97XP.XLSM containing the macro BIOASSAY97 should be put on the same directory where SOLVER.XLAM is found, i.e. C:\Program Files\Microsoft Office\OFFICE12\Library\SOLVER\ or similar. After done this, you might create a shortcut on your desktop to lunch the macro more easily.

Please, make also sure that macros are enabled in EXCEL. 1. 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." To close the Trust Center window, click the lower right "OK" button. Save and close Excel completely, reopen Excel, open BIOASSAY97.XLSM 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 BIOSSAY97XP.XLSM, click on the "Biologic assay" entry on the ribbon.

---

3. 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.

Basically, BIOASSAY97 manages two types of analysis:
1 - single dose-response curve;
2 - multiple dose response curves.
The user will use the first type when analysing one single dose-response curve, aiming at measuring ED levels, while he will use the second type of analysis when his interest is in comparing several dose-response curves.
In both cases, the user is then 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.

---

4. Single curve analysis

4.1 Model selection

A response model, a dose variable and a response dose can be selected from drop-down menus (fig. 2). Concerning models, three selections are possible: (1) Log - logistic model (sigmoidal symmetric responses on log-dose); (2) Gompertz model (logistic asymmetric responses) (Streibig et al., 1993); (3) Peaked model (Brain and Cousens, 1989; logistic responses with stimulation at low doses).
See the earlier cited papers for a detailed description of each single model and of biological meaning of parameters. Indeed, a log-logistic model would fit in most of the cases, while a Gompertz model should be selected in case of clearly asymmetric responses. The peaked model is suitable in case of response stimulation at low doses, but its usage should be carefully evaluated, as its mathematical properties are not as good as those of the other two sigmoidal models.

Figure 2. Model and variables choice.

4.2 Increasing or decreasing curves

Each model can be taken to handle both increasing and decreasing curves, such as those based on growth of test-species (that is expected to decrease, as dose increases) or on growth inhibition (that is expected to increase, as dose increases). The macro itself will decide whether an increasing or a decreasing curve is required and will provide itself for the necessary adjustments on the model.

4.3 Constraints

Constraints can be put on the lower and/or on the upper asymptotes. This might be necessary to reach convergence or to improve the estimates of the other parameters. In particular, constraints might be needed: (1) for biological reasons; (2) whenever the lower asymptote is not significantly different from 0 or negative; (3) dealing with curves based on percentage pest control, which are often supposed to range from 0 to 100%; (4) asymptotes are measured or known without any experimental error.

4.4 Starting values for parameters

BIOASSAY97 does not provides itself starting values for parameters. Basically, the highest and the lowest observed response values can be used as the starting points for the higher and the lower asymptotes respectively. A starting value for the inflection point can be relatively easily estimated from observed data (just choose a dose value which gave a response approximately half way between the higher and the lower asymptote), while a value of 1 for the slope should be considered appropriate in most cases. Likewise, a value of 1 should be appropriate for the parameter describing stimulation, if needed.
If a constrained model has been chosen, the value to which the parameter has to be constrained must be entered here. For example, if the user wants to constrain the lower asymptote to 0, such a value has to be entered in the apposite input box.

4.5 Lambda values for the transformation of response

Data do not always meet the basic assumptions for regression analyses. Tipically, whenever the difference between the highest and the lowest observed response is higher than one order magnitude, variances will not be constant across all the treatments. Among the Box and Cox (1964) families of transformations, the following one has been chosen:

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 l (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).

4.6 ED levels and confidence limits

By default, the program itself will calculate the most useful ED levels, together with confidence limits. Indeed, in the case of decreasing curves, ED10, ED30 and ED50 are calculated, while in the case of increasing curves, ED50, ED70 and ED90 are calculated. Should an additional ED level be required together with confidence limits, the corresponding response level must be entered on the appropriate input box (for example if the ED85 level is required, enter 85 on the textbox). Confidence limits are calculated by including each ED level in the model as an explicit parameter, as shown by Jensen and Streibig (1994). This reparameterisation is not possible with the peaked curve of Brain and Cousens; in this case, confidence limits for ED-levels are calculated by using the inference band for the expected response, as shown by Bates and Watts (1988) and Snedecor and Cochran (1991).

---

5. Multiple curve analysis

While the previous analyses is normally performed to estimate ED-levels, simultaneous fitting of several curves is performed when dose-response curves need to be compared on a statistical basis.
To be able to simultaneously analyse several dose-response curves, at least three column have to be included on the spreadsheet: a dose variable, a response variable and a categoric variable, coding for the different curves (see figure 3).

Figure 3. Example of database selection, when different curves have to be simultaneously fitted to observed data. In this example the performances of two herbicides are gong to be compared.

In some cases, all the curves share the same untreated check (pesticide rate equals to zero); this may happen for example when comparing the performances of several pesticides. In this case, the untreated check might be assigned arbitrarily to one of the curves, but common higher asymptotes have to be chosen for the different curves later on.
The three variables (doe, response and category) have to be selected from drop-down menus on the apposite window.

5.1 Model selection

Two models can be selected: a logistic and a Gompertz model. A peaked model has not been included, because a comparison of dose-response curves on the stimulation range is not normally carried out.. If needed, stimulation can be masked to improve the precision of estimates.
After selecting the type of model, the user should state whether the different curves have common parameters. Figure 4 shows an example wherein the user has requested the macro to consider different logistic curves with common asymptotes and slopes (parallel curves).
A lambda value should also be provided here. See single curve analyses for more information on data transformation.

Figure 4. Example of model selection.

5.2 Starting values for parameters and constraints

After selecting the model, the user is prompted to enter, for each curve, starting values for parameters. In the case that lower or higher asymptotes have to be constrained to a specific value, the apposite option button is to be selected.
For example, figure 5 shows a case in which the user has requested the macro to constrain the lower asymptote of curve 1 one to 0.
In the case of multiple curves analysis, the macro does not provide starting values for parameters, but it just memorise the lastly specified values. It has been assumed that the user has already carried out separate analyses for each curve and thus has already precise indications on possible starting values for parameters.

Figure 5. Selection of constraints on one curve.

---

6. Iteration process and results

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.

---

7. References and further reading

BATES, D.M., WATTS, D.G., 1988. Nonlinear regression analysis & its applications., John Wiley & Sons, Inc, New York.
BOX, G.E.P., COX, D.R., 1964. An analysis of transformations. Journal of the Royal Statistical Society, B-26, 211-243.
BRAIN, P., COUSENS, R., 1989. An equation to describe dose responses where there is stimulation of growth at low doses. Weed Research 29, 93-96.
DRAPER, N.R., SMITH, H., 1981. Applied regression. John Wiley & Sons Inc.
FINNEY, D.J., 1979. Bioassay and the practice of statistical inference. International Statistical Reviews, 47, 1-12.
STREIBIG, J.C., RUDEMO, M., JENSEN, J.E., 1993a. Dose-response curves and statistical methods. In "Herbicide bioassays", ed. Streibig J.C. e Kudsk P., Boca Raton, 29-55.
STREIBIG, J.C., 1988. Herbicide bioassay. Weed Research 28, 479-484.
STREIBIG, J.C., JENSEN, J.E., OLOFSDOTTER, M., HAAS, H., ANDREASEN, C., LAWAETZ, E., 1993. Testing hypotheses with dose-response curves. Proc. 8th Symposium "Quantitative approaches in weed and herbicide research and their practical application", Braunschweig, 423-431.

---

7. Aknowledgements

The author wish to thank Euro Pannacci (DSAA, University of Perugia), Ivan Sartorato (IBAF, CNR Padova), Albert Fisher (University of California) for testing the macro and providing useful comments and suggestions.

---