How to Conduct Single Factor (One Way) ANOVA Test with MS Excel 2010

© Ugur Akinci

MS Excel 2010 is capable of fairly sophisticated statistical analysis. ANOVA (analysis of Variance) is one those tests that Excel offers.

The simplest ANOVA test is one that involves one independent and one dependent variable. It’s called a “Single Factor” or “One Way” ANOVA.

The dependent variable must be an INTERVAL (integer) or RATIO type of data.

The independent variable has to be NOMINAL or ORDINAL level categorical data.

A Short Introduction to Important Concepts

In ANOVA we always test whether the NULL HYPOTHESIS is true or not.

The NULL HYPOTHESIS says that there is no difference between the mean values of the categories of independent variables.

Thus by conducting the ANOVA test, we bet AGAINST the null hypothesis; we bet that it’s WRONG and there actually IS a difference between the mean values of the categories of independent variables. That’s one and the same thing as saying that we bet the independent variable DOES make a difference in the result, outcome, or the dependent variable values.

For example, let’s assume that we are trying to assess whether lemon juice diet does in effect help people lose weight in 3 months or not.

How can we know with 95% certainty that lemon juice does help lose weight? How can we be sure that the results we see (weight loss) is not a fluke; that it can be attributed to the lemon juice diet with 95% confidence? That’s when ANOVA comes to the rescue.

Another important concept is the LEVEL OF CONFIDENCE, or STATISTICAL CONFIDENCE LEVEL.  “0.05” level of confidence means we can be certain that results are significant 95 out 0f 100 cases. In other words, we have 5% chance of being misled by the data. “0.05” is also referred to in statistical analysis as “Alpha” value.

The Example

Here is our sample data:

Why we need statistical analysis?

As you can see, by just eye-balling this data it’s not easy to determine whether drinking lemon juice causes to lose weight. In some cases drinking lemon juice did in fact cause more weight loss than the control group (compare the second row numbers: 5 vs. 3.2).

Yet, for the third and fourth cases, those who did not drink any lemon juice lost more weight (4.4 and 8.3 lbs, respectively).

Since we can’t “figure out” easily by just looking at the raw data whether what we’re saying is true or not, we need statistical analysis. We need ANOVA.

SIDEBAR: A “control group” is an important feature of all scientific tests. The control group does not get the treatment tested to see if the results attributed to the treatment occur by chance or not.

To conduct an ANOVA test in Excel 2010:

(1) Select the data table together with the column headings (“Lemon Juice Diet” and “CONTROL GROUP”).

(2) Select the DATA tab. Click Data Analysis to display the Data Analysis list box:

(3) Select ANOVA Single Factor and click OK to display the ANOVA: Single Factor dialog box:

(4) Select the Input Range of your data by either entering the absolute cell references like “$C$3:$D$13” of clicking and dragging your mouse over the range in the spreadsheet.

(5) Select Grouped by Columns option button (in this case).

(6) Enter “0.05” for Alpha level which is our level of confidence of statistical significance.

(7) For Output Range enter the cell where you want Excel to start printing the results, or click and select it with your mouse in the spreadsheet.

(8) Click OK to display the result:

“So how am I supposed to interpret all these numbers? I’m not a statistician – HELP!”

You don’t need to have a Ph.D. in mathematics to interpret these results if you know what to discard and what to focus on.

Focus only on the following THREE numbers:

F, P-value, and F crit

If F is GREATER THAN F crit, and if the P-value is SMALLER than ALPHA (the confidence level, remember?), then you can be confident that your NULL HYPOTHESIS is NOT true.

Or in other words, that your original “A makes a difference in B” hypothesis is TRUE.

In our specific example, let’s look at those values:

F (6.054) is GREATER than F crit ($.413).

P-value (0.024) is LESS than our Alpha (0.050).

Thus we can say with 95% confidence that the NULL HYPOTHESIS that “lemon juice diet makes no difference whatsoever in losing weight in 3 months” is FALSE.

By inference we can also say with 95% confidence that “lemon juice diet makes a difference in losing weight in 3 months”.

Mission accomplished.