Univariate Analysis Project

Overview

Perform a basic univariate analysis of the fake data provided in grades.xls Make sure you understand the basics of creating a bin set, using this data to build a histogram, and running basic descriptive statistics calculations on a data set.

Histogram

  1. Download and open grades.xls
  2. Copy the first column of data (final project) to sheet 2. Rename this new sheet "histo"
  3. Create bins to represent this grading scale:
    • A: 90 +
    • B: 80 - 90
    • C: 70 - 80
    • D: 60 - 70
    • F: below 60
    Note that this is not the official class grading scale. I'm just using it to simplify the exercise.
  4. Determine how many exams fall within each bin. Do this by hand or using an automatic formula
  5. Create a histogram chart displaying the grade information

Descriptive Statistics

  1. Make a second copy of the final project data. Copy this to sheet3, and rename it "descriptive"
  2. Name the range appropriately
  3. Calculate the following values using built-in formulas and label them:
    • The sum
    • The mean
    • The median
    • The mode
    • The min
    • The max
    • n (the count)
    • n - 1
  4. For each data point, calculate its difference from the mean
  5. Square each difference
  6. Sum the squares of the differences
  7. Divide the squares of the differences by n-1
  8. Take the square root of this result
  9. Calculate the standard deviation of the data set using the stdev function and verify that your calculations are correct
  10. Save your work
  11. Submit the spreadsheet to the oncourse dropbox