Note: to carry out the statistical tests described in this manual using Excel for Windows, the Data Analysis Tools must be enabled. This has already been done on the lab computers but if you are using a computer elsewhere, you may need to enable it. Go to the File tab then select Options. Click on Add-Ins. Select Analysis ToolPak, then click OK.
The Data Analysis Tools pack is only available in Office for Mac 2016 - it can't be enabled in older versions. However, all Vanderbilt students can download a free copy of Office 2016 for Mac from the software store. If you have Mac Office 2016 Excel, you can enable it as follows:
1. Click the Tools menu, then click Add-Ins.
2. Select the Analysis ToolPak from the Add-Ins available box. Click OK.
3. Restart Excel.
The Analysis tools should appear on the Data ribbon at the far right.
Baskauf, S. J. 2016. Introduction to Biological Sciences Lab (BSCI 1510L) Excel Reference and Statistics Manual. Vanderbilt University, Nashville, TN, USA. http://researchguides.library.vanderbilt.edu/bsci1510L
3.2 Samples versus populations
3.2.1 Human height example
3.2.2 The standard error of the mean (S.E.) and sample size (n)
3.3 Calculating descriptive statistics in Excel
4 Variation and differences
4.1 Getting "the answer"
4.2 Sources of variability
4.2.1 Experimental error
4.2.2 Intrinsic variation
4.2.3 Experimental effect
4.3 Describing the causes of variability
4.4 Alternative explanations of results
5 Differences in experimental science
5.1 Aside: commuting to Nashville
5.2 P and detecting differences in variable quantities
5.3 Statistical significance
5.4 A test for differences of sample means: 95% confidence intervals
5.4.1 What is a 95% confidence interval?
5.4.2 Testing for significance using 95% confidence intervals
5.4.3 Statistical power
5.4.4 Calculating 95% confidence intervals using Excel
5.4.5 An example of the application of 95% confidence intervals
5.4.6 Weaknesses in using 95% confidence intervals as a significance test
5.5 Error bars in figures
5.5.1 Examples from journal articles
5.5.2 Creating a column chart with error bars using Excel
5.6 Discussing statistics in your scientific writing
6 Scatter plot, trendline, and linear regression
6.1 Creating a scatter plot in Excel
6.2 What do we need a scatter plot for?
6.3 Regression as a statistical test
6.4 Descriptive information provided in a regression analysis
6.5 Adding the equation and R2 of a trendline in Excel
6.6 Performing a linear regression analysis using Excel
7 The t-test of means
7.1 The t value
7.2 Relationship between t and P
7.3 Performing a t-test of means using Excel
8 Paired t-test
8.1 The problem of uncontrollable variation
8.2 Performing a paired t-test using Excel
9 Two-tailed and one-tailed tests
10 Variation on t-tests: ANOVA
11 Reporting the results of a statistical test
12 Summary of statistical tests