How to Use Excel ANOVA Three-Way With Replication
by Elliott TaylorOne-way analysis of variance, or ANOVA, is a test used to determine whether the population means of a dependent variable like heart rate are different from each other at different levels of an independent variable, like temperature. Two-way ANOVA adds a second factor, like humidity, and three-way adds a third factor such as weight. In each test, the null hypothesis is that the means of the dependent variable are the same, and the alternative hypothesis is that they are different. Two- and three-way ANOVA tests also add the interaction between the factors. ANOVA with replication means that, for each combination of variables, you have taken two or more measurements. Excel can only run up to two-way ANOVA; you would need a more advanced program like Minitab, SAS or SPSS to do so. However, you can use a regression equation with three factors in Excel to approximate the results of a three-way ANOVA test with replication.
Install the Data Analysis Module
Skip to the next section if you have already installed the data analysis add-in for Excel. If not, click the Microsoft Office button at the top left of your window to begin.
Click "Add-ins" and then, from the box that pops up, select "Analysis ToolPack."
Click "OK."
Running Multiple Regression in Excel
Enter your data into a new spreadsheet, in four columns, the leftmost for your dependent variable and the next three for your predictor variables. Populate them with the sample measurements you have taken.
Click the data tab in the ribbon.
Click data analysis and then regression.
Enter the cell references of your dependent variable in the box marked "Input Y Range" and the references of your independent variables in the box marked "Input X Range."
Enter the reference of a cell two or three rows below your data table in the "Output Range" box.
Click "OK" to run the regression analysis. The output will contain an ANOVA table that will allow you to interpret whether the null hypothesis that the means of each population are equal and there is no interaction between the variables, just as you would with a one- or two-factor ANOVA test.
Tip
- check If your independent, or "predictor," variables are categorical, you will need to replace them with binary numeric values (1 for yes, 0 for no) in order to indicate the category properly in the regression model. For instance, if instead of body weight you used gender for the third variable, you could replace "male" with 1 and "female" with 0 or vice versa.
Warning
- close Both the ANOVA and regression models are only valid when the dependent variable is a continuous variable. A continuous variable is a numeric value like temperature, as opposed to a categorical value, such as gender or occupational status.
References
- link National Institute of Standards and Technology; e-Handbook of Statistical Methods; Are the Means Equal?
- link P. Mean; What's the Difference Between Regression and ANOVA?; Steve Simon; October 2008
- link University of California, Davis; EXCEL 2007: Multiple Regression; A. Colin Cameron
- link National Institute of Standards and Technology; e-Handbook of Statistical Methods; Models and Calculations for the Two-Way ANOVA
- link University of California, Davis; EXCEL: Access and Activating the Data Analysis Toolpack; A. Colin Cameron