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.
Taken from http://www.esf.edu/efb/course/efb226/Excel%20Help.htm
No comments:
Post a Comment