course mod topic first name date completed
DAT-201 wk. 1 Spreadsheet review and pivot tables
arrow_back
Data analytics 201 > Spreadsheets and projections

Spreadsheet pivot tables and descriptive stats

key points

A grading distribution can be described by a mean value and a standard deviation. A histogram counts the number of observed values in a given "bin" range and contains no spaces between frequency bars

Mini project: Vlookup and Grading Distributions

  1. Access Eric Darsow's grading history for Fall 2019
  2. Duplicate the google sheets into your own drive document or into MS Excel or Libre Office Sheets
  3. Add to your spreadsheet with Eric's grades this anonymized list of grades from a javascript class at CCAC. Best to create a separate tab for the javascript data.
  4. Study the data dictionary and the data sheets to conceptualize what the data set contains
  5. Use VLOOKUP functions in your spreadsheet of choice to populate the missing fields in the rows for both the mid-term grades and the final grades. Make sure to use the lookup tool instead of hand-classifying the data. Imagine there are millions of rows!
  6. Once you have populated data in the core spreadsheet, copy the values into a new sheet such that there are no more formulas and just "plain" numbers and letters to work with
  7. Create a pivot table which computes the mean and standard deviation of grade points for each class at the mid-term and the final grade
  8. Create a frequency distribution (a historgram) which graphically shows the distribution of the mid-term and final grades for at least two of the classes (Probably CIT-111, since there are the most students, and perhaps CIT-100 which was offered online)
  9. Compose a set of defensible statements comparing the grade distribution in each course based on the data revealed in the spreadsheet. Are the data skewed in a certain direction? Which course without point-based grading is most similar to the point-based counterpart (the Javascript)? Which is most different?