Calculating Mean and Standard Deviation in Excel

Individual variation is inherent across living organisms. However, biologists cannot sample every single organism to draw conclusions about a particular group. Therefore, when taking biological measurements from study subjects, we infer population-level characteristics from representative samples of the population. It is generally accepted that the larger your sample size, the closer you are to representing the whole (or "true") population. Therefore, we often include replicate samples of each experimental group of interest into our experimental designs.

The mean (or numerical average) is a useful way to communicate that "central tendency" of measurements from a set of replicate samples.

When reported alone, the mean may not be particularly representative of your set of sample measures, because it can be strongly affected by measurements on the low and high extremes. Therefore, it is useful to also provide an estimate of variability, or how measurements are scattered or spread around a mean. In this course we will calculate standard deviation as our estimate of variability. Both biological variation and experimenter accuracy are sources of variability in our data that could increase standard deviation. Generally (but not always!), the larger the sample size, the less variability there will be, and the smaller the standard deviation will be.

Standard Error (aka Standard Error of the Mean) is another common estimate of variability that you may see in scientific papers. Standard error takes the standard deviation divided by the square root of the sample size. Therefore, the larger the sample size, the smaller your standard error will be. The smaller the standard error value is, the more accurately our measurements represent the true population. We won't be calculating this regularly in this class, but we are happy to teach you how to calculate it if you are interested.

Mean of a Sample Population: Calculation by Hand

Step 1: Sum each observation measurement (e.g. Add up the size of each of the animals measured).

Step 2: Divide by the number of observations (n) (e.g. 8 animals)

1. Open your Excel data spreadsheet. Make sure the data are clearly organized in columns and labeled.

2. Create a summary table next to your data where you will calculate mean and standard deviation. Add the group names in the first row, averages in the second row, and standard deviations in the third row.

Calculating Averages

2A. Click on an open space on the spreadsheet to make a mini-data table to calculate the means and standard deviations for each of your groups.

2B. Click in the first box in the first row of the mini-table and type the formula (using an equal sign): =AVERAGE(

Then Highlight the relevant range of data of interest and press Enter. The calculated average for measurements of the highlighted replicates will appear.

2C. Repeat on each set of data for which you would like an average.

Calculating Standard Deviations

2D. Click in the first box in the second row of the mini-table and type the formula (using an equal sign): =STDEV(

Then highlight the relevant range of data of interest and press Enter. The calculated standard deviation for measurements of highlighted replicates will appear.

2E. Repeat on each set of data for which you would like a standard deviation.

In text, you will report mean value followed by a "plus or minus" sign (±) and then the standard deviation value for each group.

You can find the ± sign in Microsoft Word by going to the top menu: Insert > Symbol. Find it in the symbol list, and click on it. Make sure that your cursor in your Word document is set to the correct location when you insert the symbol!

Example text: The mean population size of clams in Group A was 320 ± 54 and the mean population size of clams in Group B was 90 ± 8.