Close |
|
How to compare two means using Excel |
|
|
|
This tutorial
will take you through the steps needed to use Excel to compare two
sets of measured data.
I have used some sample data
from an investigation on the effect of two fertilisers on potato growth.
Click on the link to open the spreadsheet. You can substitute your
own results if you have them. |
|
- Launch Microsoft Excel.
- Type in a heading for your first (input) variable in cell A1.
Don’t forget the units!
- Type in a heading for your second (outcome) variable in cell
B1.
- Now enter your data down columns A and B.
- Save your file.
|
|
|
|
- Enter formulae to calculate means for each set of data in the
row below the last data items.
The general folmula is:
=AVERAGE(RANGE)
e.g. =AVERAGE(B2:B11)
The example shows an extra column used to clarify the headings.
If you want to do the same thing, insert a column to the left
of the data.
|
|
|
|
- Enter formulae to calculate the standard deviations for each mean.
The general formula is:
=STDEV(RANGE)
The standard deviation gives an indication of the degree of spread
of the data around the mean. A high standard deviation suggests
that there is a lot of variation in the data.
The cells in the example have been formatted as number
with the contents displayed to 2 decimal places. Select the cells
and click on Format Cells… Number and then check
that 2 decimal places is selected.
|
|
|
|
It
is useful to display the data as a bar graph with error bars showing
the standard deviations. The standard deviations give an idea of the
shape of the distribution around each mean and the degree of overlap
may help you decide if they are significantly different. |
|
- Highlight the two cells containing
the two means. Then click on the Chart Wizard button in the toolbar
or select Insert and then Chart…
- Choose the column chart
type and the clustered column sub-type in the Chart Wizard
that appears.
- Click on the Next button
to go on.
|
|
|
|
- The chart preview in the next
step should look about right. If it doesn’t, cancel and check that
you selected the correct cells before starting the chart wizard.
- Click the Next button.
|
|
|
- Enter appropriate titles and labels in the Titles
section of the next step.
- Click on the Gridlines tab and turn off any
gridlines by making sure that all the tick boxes are empty.
- Click on the Legend tab and clear the tick
box that displays the legend.
- Click on the Next button.
|
|
|
|
- In the final step, choose the option to place the
chart as an object in the current sheet and then click on the Finish
button.
|
|
|
|
- Right-click on one of the bars and select Format
Data Series… from the menu that appears.
- Select the Y-error bars
tab and select Display Both (i.e. plus and minus).
- Select the Custom option
and then use the button at the end of the + box to select
the cells that contain the standard deviations. Repeat this for
the – box.
You may want to tidy up the
graph in other ways. Right-click on an object that you want to
change and select the object’s formatting dialog.
|
|
|
|
- Finally we can test the null hypothesis that there
is no difference between the two means using the t-test.
The general formula is:
=TTEST(RANGE1,RANGE2,2,2)
The numbers at the end indicate
the type of test to be performed. If your data items are paired
e.g. heart rates of people before and then after a meal, end the
formula with 2,1.
|
|
|
|
The cell containing the t-test formula has been
formatted as percentage. This makes the result easier to interpret. |
|
|
The
t-test gives the probability that the null hypothesis is true. So,
a value of 5% or greater suggests that there is no significant difference
between the means. Any actual difference is likely to be due to chance.
If the value is less than 5%, an
alternative hypothesis needs to be made since the means appear to
be significantly different since they do not overlap significantly. |