Use of a Spreadsheet for Statistical Calculations

Background: While there are many highly specialized statistical programs available the results of simple tests can be calculated using a spreadsheet program, such as MS-Excel. This assignment uses data from the course Data Interpretation (CDENT 5111) in order to perform such calculations. You will also learn how to convert a flatfile to a small database stored in a simple Excel workbook in order to understand the difference between the two different data storage types.

How to complete this assignment: You will transfer the data from the provided dataset to an MS-Excel spreadsheet. Then, using MS-Excel calculate the mean, median, mode, variance, standard deviation, range, quartiles and interquartile range. Finally, attach the MS-Excel file to an email and send it to both instructors, to the one teaching this course for an evaluation of the usage of MS-Excel and to the one teaching CDENT 5111 to evaluate the actual results.

Details

• Dataset
  You will receive a dataset from Dr. J. Close, course director of CDENT 5111, via email. The dataset will be in ASCII format. Here is a sample dataset.

• Transfer to MS-Excel
  After saving the attachment from Prof. Close's email to a storage media (e.g. hard drive or floppy) you need to open it in MS-Excel via the File/Open menu. Make sure you specify as file type All Files (*.*). MS-Excel's Text Import Wizard will guide you through the import process which is characterized by a fixed width import (fields are aligned columns with spaces between each field). The data preview step of the wizard allows you to adjust the width with a slider. Now, after the import, you want to store your file as Microsoft Excel Workbook file using the Save As menu under File (see Fig. 1).
  
  
  Fig. 1: Save As Microsoft Excel Workbook file after successful import of data.

• Calculations
  Calculate the mean, median, mode, variance, standard deviation, range, quartiles and interquartile range by using either equations you build or by using the integrated functions in Excel (Insert/Function). In case you use the build-in functions you will need to write a comment to each solution using MS-Excel's comment feature explaining the formula (see Fig. 2). If you don't use the build-in functions, you need to explain only the meaning (definition) of what you have calculated using the comment feature.
  
  
  Fig. 2: Using MS-Excel's comment feature a description was added.

• Formatting
  Be creative when formatting your Excel spreadsheet. However, do not add so called "chart junk" to the spreadsheet. Use only formatting which supports the message and makes data review easy (see Fig. 3).
  
  
  Fig. 3: Sample formatting of an Excel spreadsheet to support data interpretation.

• Submission
  Pease provide a hardcopy to Prof. Close and send your completed Excel spreadsheet as email attachment to Ryan Shaw (rshaw@dental.pitt.edu) with the subject line "Assignment 3". Remember, professional electronic communication requires a cover email message properly explaining what you are sending.
  

Grading
You will receive two grades for this assignment: One for the correctness of your equations/results—please contact Prof. Close for details. And, a second grade for the successful transfer of the data to a spreadsheet, the proper formatting of the spreadsheet, the appropriate use of comments and the use of an appropriate text and format in the cover email message accompanying your assignment submission. Late submissions will be automatically graded one letter grade lower. You will receive your grade shortly after submitting your assignment—check the grades page.