Using Spreadsheets for Basic Statistical Tests in Biology
Fundamentals of Statistical Analysis and the Mean
Definition of the Mean: The mean is commonly referred to as the average. It is a statistical measure used to determine the overall trend of a dataset, particularly when the numerical values within that set are relatively similar to one another.
Case Study: Project Grades: To calculate the mean for a specific set of class grades, consider the following data points representing project scores:
, , , , , , , , , , , , , , and .
Procedure for Calculating Mean in Google Sheets:
Open a new spreadsheet and title the second column "Grades on Project."
Populate the column with the data points, ensuring only one grade is entered per row.
Select the entire column containing the data.
Navigate to the formulas tab (located at the top right on a computer or at the bottom of the iPad app).
Select the Average function and press enter.
The resulting value produced in the subsequent row is the mean of the dataset.
Label this calculation by typing "mean" in the first column adjacent to the calculated value.
Understanding Standard Deviation in Data Distribution
Definition of Standard Deviation: This is a numerical value that illustrates how closely data points are clustered around each other and the calculated mean.
A small standard deviation indicates that the numbers in the set are very close to one another.
A large standard deviation indicates that the numbers vary significantly and are spread out over a wider range.
Sample Size Constraints: Standard deviation calculations should ideally be reserved for datasets containing at least or more numerical points. While it is possible to calculate standard deviation with fewer data points, doing so increases the likelihood that the result will reflect a higher margin of error in the experiment.
Case Study: Leaf Growth in Sunlight versus Shade: An experiment was conducted to determine if leaves grew longer when plants were placed in direct sunlight. The hypothesis stated that leaves in the sun would exhibit greater length than those in the shade. The data collected was as follows:
Leaves in Shade (Length in mm): , , , , , , , , , ,
Leaves in Sunlight (Length in mm): , , , , , , , , , ,
Calculating Standard Deviation in Google Sheets:
Label two columns on the spreadsheet as "Shade" and "Sunlight."
Input the corresponding leaf length data under each labeled column.
To calculate the standard deviation for the "Shade" column, click an empty cell within that column.
Type the command
=STDEVand select it from the resulting menu.Select the range of numbers in the "Shade" column and press Enter.
Repeat these steps for the "Sunlight" column data.
Standard Error of the Mean and Statistical Significance
Standard Error (SE): The standard error of the mean () indicates how closely the sample mean approximates the true mean ( mean) of the entire population from which the group was drawn.
The Range and Confidence Intervals: The calculation of mean defines a range of values. This range provides a confidence level that the true population mean (the "real answer") resides within those boundaries.
Calculating in Google Sheets: Since Google Sheets lacks a native preset formula for , it must be calculated manually using the following procedure:
Select an empty cell in the relevant column (e.g., "Shade").
Determine the total number of data points () used for the mean and standard deviation calculations.
Use a calculator to find the square root of that number ().
In the empty cell, input the formula:
.Press enter to generate the value.
Repeat this process for the "Sunlight" dataset.
Data Visualization and Error Bars
Purpose of Error Bars: Error bars are utilized on graphs to visually demonstrate the variability of data. They serve as a tool to determine if two sets of data are significantly different from one another.
Determining Statistical Significance via Overlap:
If the error bars of two different groups overlap across many of the same numerical values, the groups are considered very similar. In this case, there is no significant difference between them, and it implies the independent variable did not meaningfully affect the results.
If there is no overlap between the error bars, the difference between the means is considered statistically significant.
Constructing Error Bars:
Upper Line of Error Bar: Calculated as .
Lower Line of Error Bar: Calculated as .
When graphing, these points are marked with horizontal lines and connected by a vertical line passing through the mean bar.
Graphing Standards: All graphs must include:
Properly titled axes.
A descriptive overall title for the graph.
A clear key identifying the data sets.
Bars and error bars drawn precisely, ideally using a ruler.
Practice Application: Plant Stomata Analysis
Experimental Context: In an Advanced Placement (AP) Biology class, students investigated whether plant species affected the quantity of stomata found on leaves. Five species were collected from the same environment, and three identical-sized areas were analyzed for each leaf.
Stomata per Examination Area Data:
Maple: Sample Area #1: ; Sample Area #2: ; Sample Area #3:
Oak: Sample Area #1: ; Sample Area #2: ; Sample Area #3:
Ivy: Sample Area #1: ; Sample Area #2: ; Sample Area #3:
Hosta: Sample Area #1: ; Sample Area #2: ; Sample Area #3:
Dandelion: Sample Area #1: ; Sample Area #2: ; Sample Area #3:
Analytical Requirements: For each species (Maple, Oak, Ivy, Hosta, and Dandelion), it is necessary to calculate the mean (), the standard deviation (STDEV), and the standard error of the mean () to determine if the species significantly impacts stomata density.