Homework 5: Terry Stop Analysis of Officer YOB

The goals of this homework are to demonstrate your ability to :

• Use various counting and statistics functions.
• Produce simple graphs
• Document your findings.

Instructions

1. You will be working with a Terry Stop dataset
2. Download the raw data file
   • Rename the worksheet to be Raw Data
   • Right now save it as an excel file.
   • Call it terry.xlsx
3. This is a large data set, so you will probably want to navigate with
   • ctrl-end takes you to the end of the data set
   • ctrl-home takes you to cell a1
   • From home, ctrl-shift-end will select the entire data set.
   • Try these commands right now.
4. Download this workbook
   • You will need to merge the two workbooks.
   • The easiest way to do this is to have the two workbooks open.
   • Grab the about worksheet in the TerryAbout workbook and drag it to the terry workbook.
   • 
   • 
   • When you finish, you should have two worksheets in a single workbook.
   • Save this again, just to be sure.
   • In this case there are 26,043 records so I don't want to make a copy. We will work in the RawData worksheet.
5. Name the columns in the RawData worksheet.
   • You will be working exclusively with the Officer_YOB field.
6. Add a comment to the About worksheet stating that the Analysis Officer YOB worksheet is the basic analysis of the officer year of birth field.
7. Add your name to the About worksheet.
8. Start a new worksheet for Analysis of Officer YOB
   • Name the sheet appropriately
   • Insert a comment in the first cell.
9. Count the various types of data in the Officer YOB field.
   • 
10. Perform a basic statistical analysis of the Officer_YOB field
    • I found that mode.mult did not work well here so don't include that.
    • 
11. I found a histogram less than useful, so create your own.
    • In cell A20 put Year
    • In Cell A21 put 1996 (the maximum year)
    • In Cell A22 put 1995
    • Drag these down to Cell A71, to get down to year 1946
    • In cell B20 put Count of Officer
    • Enter a formula (ie countif) to count the number of officers born each year.
    • 
    • Build a bar chart to display this data.
      • Be careful of your chart selection, do not graph the year of birth.
      • Make sure to give a useful title to the chart.
      • I don't think axis labels are required.
      • But Data labels are a good thing.
      • 
12. Build a box and whisker plot of the officer YOB
    • I adjusted Series Options (series1) to only show outlier points and mean markers.
    • I adjusted the Vertical Value Axis to start at 1900 and end at 2000.
    • I adjusted the color so I could see the numbers.
    • I adjusted the series 1 data labels to be numbers with no decimal points and no commas.
    • 
13. (This is worth 20% of the homework) Using word, provide an analysis of the Officer Year of Birth field of this dataset. Use the example from the notes as your guide.

Submit