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:

    • 9292, 9494, 9595, 8888, 9191, 8989, 9393, 8787, 9090, 8686, 8585, 8484, 8383, 8282, and 100100.

  • 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 55 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): 24.124.1, 26.326.3, 27.227.2, 30.730.7, 31.831.8, 32.432.4, 32.532.5, 33.333.3, 37.737.7, 38.538.5, 43.243.2

    • Leaves in Sunlight (Length in mm): 33.133.1, 34.934.9, 35.235.2, 35.335.3, 36.536.5, 36.236.2, 36.436.4, 37.537.5, 41.341.3, 42.142.1, 45.745.7

  • 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 =STDEV and 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 (SExSE_x) indicates how closely the sample mean approximates the true mean (xx mean) of the entire population from which the group was drawn.

  • The 2SEx2SE_x Range and Confidence Intervals: The calculation of mean ±2SEx\pm 2SE_x defines a range of values. This range provides a 95%95\% confidence level that the true population mean (the "real answer") resides within those boundaries.

  • Calculating 2SEx2SE_x in Google Sheets: Since Google Sheets lacks a native preset formula for 2SEx2SE_x, 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 (nn) used for the mean and standard deviation calculations.

    • Use a calculator to find the square root of that number (n\sqrt{n}).

    • In the empty cell, input the formula: =2×(STDEV Cell/n)=2 \times (\text{STDEV Cell} / \sqrt{n}).

    • Press enter to generate the 2SEx2SE_x 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 2SEx2SE_x 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 mean+2SEx\text{mean} + 2SE_x.

    • Lower Line of Error Bar: Calculated as mean−2SEx\text{mean} - 2SE_x.

    • 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: 8888; Sample Area #2: 9393; Sample Area #3: 7878

    • Oak: Sample Area #1: 9494; Sample Area #2: 7575; Sample Area #3: 8282

    • Ivy: Sample Area #1: 6868; Sample Area #2: 7272; Sample Area #3: 5454

    • Hosta: Sample Area #1: 7878; Sample Area #2: 7171; Sample Area #3: 6565

    • Dandelion: Sample Area #1: 6363; Sample Area #2: 5858; Sample Area #3: 7171

  • Analytical Requirements: For each species (Maple, Oak, Ivy, Hosta, and Dandelion), it is necessary to calculate the mean (xx), the standard deviation (STDEV), and the standard error of the mean (2SEx2SE_x) to determine if the species significantly impacts stomata density.