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.
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 |
The XL Toolbox also offers one-way analysis of variance with posthoc tests.
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:
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.
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).
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.
When you click on “Create report”, a new worksheet ply will be created that contains computational results for the ANOVA in a tabular format.
Currently, the 2-Way ANOVA of the XL Toolbox has the following limitations. They will be addressed in a future release.
If the ANOVA computation fails for any of these reasons, you will be informed by a dialog box.
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.
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).
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” |
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” |
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)