Use the two-way (or two-factor) analysis of variance if there are two independent variables in your experiment, for example genotype and treatment, or treatment and time. Contrary to other statistical packages such as SigmaStat® or Graphpad Prism®, the XL Toolbox does not require to arrange your data in one particular order.

To our knowledge, the XL Toolbox is the only free statistics addon for Excel that supports repeated-measures (RM) ANOVA.

Data entry: maximum possible flexibility

For the 2-way ANOVA, you can essentially enter your data as you acquire them. For example, if you want to study the effect of a Drug and a Genotype on the glycemia (in mg/dl) of several mice, you could enter the data as in the following example. There is one row per mouse, and each mouse is identified with its genotype and treatment.

Genotype Drug administered
  No Yes
Wildtype 155 131
Wildtype 149 132
Knockout 182 143
Wildtype 152 117
Knockout 176 139
Knockout 184 136

As you can see, the genotype does not need to be sorted. If you wish, you can add additional columns for the drug treatment, if your data entry method necessitates this:

Genotype Drug administered
  No Yes Yes No
Knockout 182 143 135 191
Wildtype 152 117 125 181
Wildtype 155 131 129 178
Wildtype 149 132 133 169
Knockout 176 139 141 190
Knockout 184 136 117 187

You can download an example data set with actual data to test the ANOVA: 2way_anova_xltoolbox.xls.

It is also possible to arrange data in a list format, where the treatment group labels are combined in one column, and the data is in another column. Some examples for possible combinations of group labels:

Knockout, treated
Wildtype__/__untreated
Wildtype & treated
Knockout - untreated

It is not possible (and doesn’t really make sense) to use several ways of combining labels for one data set.

Example:

Genotype - Drug administered Measurement
Wildtype - untreated 155
Knockout - untreated 131
Wildtype - treated 149
Knockout - untreated 131
Wildtype - treated 132
Knockout - treated 178

1-Way ANOVA

The XL Toolbox also offers one-way analysis of variance with posthoc tests.

Running the analysis of variance

In Excel 2000 through 2003, you run the 2-way ANOVA from the XL Toolbox menu.

In Excel 2007, click on the bottom part of the “ANOVA” button on the ribbon to open a menu with two items, and choose “2-Way ANOVA”.

In the 2-Way ANOVA dialog (see screenshot) you can enter the following parameters:

  • Data range, with or without labels: A rectangular range of cells that may or may not contain labels.
  • Factor A Labels: Factor A is the “vertical” factor, e.g. the genotype in the example above. Each subject needs to have a Factor A label. Labels can either be read from the data range, or, if for some reason they are separated from the actual data (e.g. by body weight data or comments, etc.), you can enter the Factor A label range here.

In the example data set (cf. screenshot ), the data cell range was entered as D7:L39, which included the horizontal labels for Factor B (time). The cell range for Factor A (vertical; Treatment Group) was entered separately as B8:B39, because it is separated from the data cells by a ‘comment’ column.

  • Factor B labels: Factor B is the “horizontal” factor, e.g. the drug treatment in the example above. Each subject needs to have a Factor B label. Labels can either be read from the data range, or, if for some reason they are separated from the actual data (e.g. by body weight data or comments, etc.), you can enter the Factor A label range here.
  • Repeated measures: Indicate if some of your data are repeated measures. The options are self-explanatory.
Excel 2007 buttons for the ANOVA commands

Excel 2007 Ribbon Buttons showing the ANOVA commands

Demo data for the 2-Way ANOVA

2-way ANOVA demo data

How the results are presented

The results of the variance analysis are presented in dialog box. The example on the right shows actual results from an example data set ( 2way_anova_xltoolbox.xls).

Create text box

From the Results Dialog, you can create a text box on your spreadsheet that includes the P values from the 2-Way ANOVA. Cut and copy this textbox into your charts.

Create report

When you click on “Create report”, a new worksheet ply will be created that contains computational results for the ANOVA in a tabular format.

Results of the 2-way ANOVA of the demo data.

Results of the 2-way ANOVA performed on the demo data

Limitations

Currently, the 2-Way ANOVA of the XL Toolbox has the following limitations. They will be addressed in a future release.

  • Equal replication required: Each of your groups must contain the same number of subjects.
  • No missing values accepted: Your data cell range must not contain any missing values.

If the ANOVA computation fails for any of these reasons, you will be informed by a dialog box.

Traditional vs. computational formulas

An analysis of variance involves calculation of sums of squares. In order to circumvent the need for iterative calculations, computational formulas have been developed. These generally work well, but are bound to produce rounding errors in certain situations. The XL Toolbox does not use computational formulas and performs all calculations with maximum possible precision (technically, ’DOUBLE’ data type).

If you find that the ANOVA results differ between the XL Toolbox and another statistical software that you may use, please check if this is an issue with the XL Toolbox or your software, as rounding errors are generally possible in every software.

Example for possible rounding errors in statistics programs

As an example for possible rounding errors, here are sums of squares (SS) and P values (P) for a 2-way ANOVA without repeated measures on the Mice-and-Cheese body weight data (2way_anova_xltoolbox.xls).

Factor A (Group)
  SS DF MS F P
XL Toolbox 385.41 2 192.70 51.33 7.16E-20
Analysis Toolpak 385.41 2 192.70 51.33 7.16E-20
SigmaStat® 385.41 2 192.70 51.33 “<0.001”
Graphpad Prism® 384.6 2 192.30 51.23 “<0.001”
Factor B (Time)
  SS DF MS F P
XL Toolbox 820.84 10 82.08 21.86 1.92E-30
Analysis Toolpak 820.84 10 82.08 21.86 1.92E-30
SigmaStat® 820.84 10 82.08 21.86 “<0.001”
Graphpad Prism® 822.4 10 82.2 21.91 “<0.001”
Interaction
  SS DF MS F P
XL Toolbox 112.22 20 5.61 1.49 0.081
Analysis Toolpak 112.22 20 5.61 1.49 0.081
SigmaStat® 112.22 20 5.61 1.49 0.081
Graphpad Prism® 112.4 20 5.62 1.50 0.080

Evidently, in this test, Graphpad Prism® 4 produced different results than three other programs. You can download the Excel file 2way_anova_xltoolbox.xls to perform these calculations yourself. (To do this, you need to have licensed copies of SigmaStat® and Prism® of course.)

I do not know whether the Graphpad team use the computational formulas in their program. In most cases, the differences will be minor and, as in this case, will not affect the conclusions. But there may be borderline results (e.g., p=0.051 or p=0.049) where small differences in p values may be important, at least in a scientific world that is all about p values and asterisks.


(Note: My nickname in the comments is bovender. — Daniel)

comments powered by Disqus