Calculating
and plotting mean and standard deviation in Excel
Print or open this Excel file
Let’s take a simple experimental example. Imagine that
your lab group decided to carry out an experiment investigating the effect of Solvay
substrate on the growth of bean plants in comparison to potting soil. You decide to use two treatments: 1) regular
potting soil (75%) with 25% Solvay substrate and 2) potting soil (100%).
You plant the seedlings and
measure the height in centimeters of 3 seedlings per treatment for six weeks.
To calculate the mean for each treatment:
List the number of weeks in
a vertical column. Head the next 3
columns with treatment 1 and replicate number.
Head the next column with “Treatment
1 Mean” and the next column with “Treatment
1 standard deviation.” Enter the
data for each of the three replicates.
Place the cursor in the
first cell under the column labeled “Treatment
1 Mean.” Go to the Insert toolbar at the top and scroll
down to Function. Type “average”, then click on AVERAGE,
OK. A dialog box will come up. Click on the square on the right hand side,
then the first cell in the sheet to average, and drag across to the last cell
that you want to average. Click OK.
The mean should appear in the cell.
The mean of the three replicates for Week 1 should now be present in the
first cell under “Treatment 1 Mean.”
To automatically calculate
for the rest of the weeks, click on that cell and then place your cursor at the
lower right hand corner of the cell. It
should turn to a cross. Click and drag
down to the sixth cell (the sixth week).
The mean for treatment 1 for each week should automatically calculate.
To calculate the standard deviation for each
treatment:
What the heck is standard
deviation, anyway? Standard deviation is
a measure of spread or variance in your data.
68% of all observations will fall within 1 standard deviation. 95% of observations for a given data set will
fall within two standard deviations. If
standard deviations between treatments do not overlap, that means that there is
probably a significant difference in the means between the treatments.
Place the cursor in the
first cell under the column labeled
“Treatment 1 standard deviation.” Go
to the Insert toolbar at the top and
scroll down to Function. Type “standard deviation”, then click on ST
To automatically calculate
for the rest of the weeks, click on that cell and then place your cursor at the
lower right hand corner of the cell. It
should turn to a cross. Click and drag
down to the sixth cell (the sixth week).
The standard deviation for treatment 1 for each week should
automatically calculate.
Now repeat the entire process of calculating mean
and standard deviation for treatment 2.
You
now have a dataset to graph!
Copy and paste the 4
columns for mean and standard deviation for treatments 1 and 2 to a separate
area below. When you paste, choose “Paste Special” and click “values.” Also paste the weeks in to the left of the
values.
Go to the Insert toolbar at the top and scroll
down to Chart. Click
Line, Next, Series, Add, and in the Values,
highlight the Treatment 1 Mean
values.
Click ADD to add another series. Do
the same as above, only highlight the Treatment
2 Mean values.
Enter labels for the X and Y axes and legends as
necessary.
Click Finish. The graph will
appear where you designate.
Now, to enter the standard
deviation valuesono your graph. Click on
any point in treatment 1 to highlight that series. Right
click and hit “Format Data Series.” Click on “Y
error bars,” “both,” and “custom.”
Then, hit the square to the right of the box for the + values and
highlight the cells in the sheet corresponding to the standard deviation for
Treatment 1. For the – values, highlight
the same cells. Click OK.
Repeat for treatment 2.
You should now have a graph
with means and corresponding standard deviations for treatments 1 and 2.