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