Understanding Standard Deviations: A Learning Activity Using Microsoft Excel

Maryann S. Allen
Colby-Sawyer College
Author Profile
This material was originally created for Starting Point: Teaching Economics
and is replicated here as part of the SERC Pedagogic Service.

Summary

In this activity, students will use Microsoft Excel to breakdown the formula for calculating the standard deviation of a data set by calculating the formula in a step-by-step manner. Students will also calculate the standard deviation using the built-in function in Excel. After all of the formulae are entered into the spreadsheet, some of the data values will be changed, and students will observe the resultant affect on the calculated standard deviation. Students will also construct Box Plots and observe how boxplots change as standard deviation changes.


Learning Goals

The primary goal of this learning activity is for students to analyze the equation for the standard deviation so that they understand why it is a measure of the dispersion of data. Additional concepts include using Excel to enter formulas and functions, and using a spreadsheet to conduct "what-if" analysis.

Context for Use

This activity is used in an introductory statistics class, and in a quantitative reasoning class. It would also be appropriate for a course on spreadsheet concepts. The activity generally requires between 30 and 45 minutes to complete, and will require that all students have access to a computer with Microsoft Excel.

Description and Teaching Materials

There are two files with this activity: (1) an Excel spreadsheet containing the data, and (2) a Word file containing the instructions.
Student Instructions for Standard Deviation Activity (Microsoft Word 2007 (.docx) 83kB Jun4 10)
Excel file for Standard Deviation Activity (Excel 2007 (.xlsx) 11kB Jun4 10)


Teaching Notes and Tips

Depending upon student's prior experience using Microsoft Excel, it may be necessary to give an introductory segment on entering equations.

Assessment

References and Resources