In this example we have five different ropes, ropes A through E, and each rope four different
tests were conducted to figure out its breaking strength. We want to use A Single Factor Analysis
of Variance to figure out if the mean breaking strength of any of the five ropes is different
from one another. Before conducting any statistical test always examine your data graphically.
To do this in Excel we'll select all of our data, and I'll go insert a line graph, and
the original graph, so it will plot ropes A through E, and it will plot the four measurements
for all five of those ropes, and the original graph is pretty lousy looking, pretty crappy.
So I've cleaned it up by including the axis labels of breaking strength with units in
pounds on the vertical axis, and then the rope type on the x axis, ropes A through E.
With an analysis of variance what we're trying to determine is whether or not the data points
for all five of the rope types come from a normal distribution with a population mean
that is the same. In other words I've got my population means for ropes A through E.
We're trying to figure out if the data points measured for all five of these ropes come
form normal distributions with the same population mean, and while it is true that this is possible
for our case that all five of the ropes came from the same normal distribution with the
same population mean, it looks to be fairly unlikely. For example for rope D, although
it is possible that these four data points could have been collected at the low end of
the normal distribution for rope D, and for rope C these for data points could be here,
and for rope A and rope B it is possible that this could occur. However it would be highly
unlikely that these data points would be collected at the low end of the populations for both
ropes C and D. When we run the analysis of variance there is a high probability that
we could reject the null hypothesis, that the breaking strength for all five ropes is
the same. What is likely to occur is that we'll get a relatively low p-value for this
analysis indicating that at least one of the population means is different from the other.
So I'll move the chart out of the way to run the analysis. One thing we'll want you'll
want to make sure that under data that you've go the data analysis tools. If you don't have
it do a search for the data analysis package for Excel and how to activate it. So click
data analysis, and the following window will appear. We want ANOVA Single Factor selected,
so with that selected click OK, and it will ask us the input range for the single factor
ANOVA, so for the input range with this highlighted select all the data with the labels in the
top row. So we're going to group, all our data are grouped by column, so row A through E
each one has it's own column, and we've got the labels being rope A through E in the first
row, so make sure that's selected, and if we want to use the default value of alpha
for 0.05, just leave it as is. In the output range you can either output this as a new
worksheet or a new workbook, but usually is seems to be easiest to select an output range
on the same worksheet. So here I'll select an output range that starts out at I1, I am
just giving it a clear are to work with, and I will click OK on that, and the ANOVA table
is shown here, so I'll select all of these, double click it to expand the columns, it
makes it, just makes it a little bit easier to see all the data. So I got ropes A through
E, my analysis of variance table shown here, and usually what I find most useful is the
p-value. In this case we find a p-value of 0.00076, which is a great deal less than our
alpha of 0.05, and this gives us a good indication that we could reject the null hypothesis, and
it gives us a good indication that at least one of the population means is different from
the other, and we will look at the data graphically. It does appear that rope C and D are weaker
that ropes A, B, and E, and this is what is causing this relatively low p-value.