• Similar to the example I present in the lecture with Suicides in NYC, here is an example with reporting gun injuries that is (somewhat) recent to demonstrate that these problems still exist in agency data:
  • https://fivethirtyeight.com/features/how-one-hospital-skewed-the-cdcs-gun-injury-estimate/
• Please read through this article by the Marshall Project.
  • Crime in Context: Violent crime is up in some places, but is it really a trend?
  • You can click in the graphics in the article to see information for different cities/contexts.
• Additionally, read this article by Dr. Steven Levitt titled Understanding Why Crime Fell in the 1990s: Four Factors that Explain the Crime Decline and Six that Do Not
  • https://www.aeaweb.org/articles?id=10.1257/089533004773563485

Criminal Justice Agency Databases

• Summary of the Uniform Crime Report (UCR)
• UCR & National Crime Victimization Survey (NCVS)
• Overview of Why UCR Changed Rape Definition
• A Guide to Understanding the National Incident-Based Reporting System (NIBRS)
• NIBRS Quick Facts
• Map of Agencies Participating in NIBRS 2018: https://nibrs.fbi.gov/2018/
• Crime Data Explorer—Who Reported Crime Data to UCR/NIBRS in 2018: https://crime-data-explorer.fr.cloud.gov/

• 30 FAQs About NIBRS Transition

• Lab 1 Part 1 Instructions (PDF)

Lab 1, Part 2

• Lab 1 Part 2 Worksheet (Excel)
• Lab 1 Part 2 Instructions (PDF)

Additional Resources

Part 1: Homicides in Baltimore

Data Entry

a. I will refer to a cell by its name in Excel. For example, Cell B4 is in column B and row 4.

b. A range of cells selected together is defined by its “address,” which is the first cell and last cell in the rage of selected cells. So, if we select all the cells B4, B5, C4, and C5, the address would be “B4:C5” because it contains the cells from B4 to C5.

a. Type Homicide Rates in Baltimore, 2010–2019. Press Enter.

i. Note: The title does not fit in the cell B4 and spreads across several columns. If a label does not fit in a cell, it will display the remaining text in the next cell(s) as long as they are empty. Otherwise, the label is truncated or cut off.

b. Click cell B5 to make it the active cell and type Year.

c. Click cell C5 and type # of homicides.

d. Click cell D5 and type Estimated Population.

a. Some of the population figures will be too large for the standard cell width, so they will be displayed with a series of number signs: ####. The data are there; it’s just that the column is too small to display all of the numbers.

a. To insert a row, move the cursor to row number 5. At the beginning of the row is the shaded box with the row number 5 inside. Click on the shaded box 5 and the whole row will become highlighted. Right click on the row and select Insert to add in a new row.

a. Select the range beginning with the Year label down to the cell containing the last year of 2019 (cell range B6:B16). Under the Home menu at the top of the page, with the range highlighted, select Center from the Alignment group in order to center the text in this group.

a. First, we will extend the width of these two columns. To do this, select the range of cells from C6:D16. Select Format from the Cells group at the top of the page, and then select Column Width. A box appears with the default width. Press the delete key to clear the box and type 22 to make it the new column width, then select OK.

a. To do this, click on the cell which has the label for the # of homicides column. Select the Wrap Text button from the Alignment group at the top of the page. Do the same for the label for Estimated Population.

a. In cell E6, type in Homicide Rate and with the cell active, select Wrap Text. Save your workbook again, and now we’ll move onto something slightly more complicated.

Entering Formulas

a. Formulas can contain more than one arithmetic operator and allow for the use of parentheses. In these circumstances, Excel decides which operations to perform first. Multiplication and division are done first, based on a left-to-right flow. Addition and subtraction are performed after that.

a. Example: 25 motor vehicle thefts per 100,000 people each year.

$Rate = (\frac{number of reported crimes}{total population}) \times 100,000$

a. Note: this is for rates per 100,000 people, which we will be using in this lab. However, this can be changed to represent any other interval of people we want, like 1,000 or 100.

$Rate =( \frac{233}{620,915}) \times 100,000 = 35.9$ homicides per $100,000$ people.

$Formula = (\frac{C7}{D7}) \times 100,000$

a. Make E7 the active cell. In that cell, type: =(c7/d7)*100000

b. Then hit Enter.

c. Using cell referencing in formulas is convenient because instead of having to type a new formula for each additional year, Excel can now transfer the same formula down the rows for the same column, which saves you from writing a new formula for each year.

d. To use cell referencing in formulas, make E7 the active cell. Select Copy from the Clipboard group (the icon is right below the cut icon and looks like two overlapping pieces of paper). [Note: You can also 1) simultaneously hold down the CTRL and C buttons or 2) right click and select Copy].

e. Now select the range E8:E16. When the range is highlighted, select Paste from the Clipboard group.

f. The formulas for the rate for each year are now created, and the rates are displayed. Excel simply updated your original formula for each row, which saves you a lot of time and effort. Press ESC so that the cell you just copied is no longer active.

a. To change the decimal place and round the numbers off, select the range E7:E17.

b. Under the Home category, in the Number group, find the button for decreasing the decimal place (called Decrease Decimal). Keep clicking on the button until only one decimal place is displayed. Save your work.

Using Excel Functions

a. In the top ribbon, select the Formulas tab. In that tab, position the cursor over the Auto-Sum button, the box with the capital Greek letter E ( ∑ ), and click/select it.

b. Auto-Sum sets up the sum function to add values in the cells above the cell pointer.

c. Here the formula =SUM (C7:C18) appears in the cell, which is the full column under the label. Verify this range and press Enter. The results now appear in the cell: 2,743 is our total number of homicides from 2010–2019.

a. Make cell B20 active and type Average into the cell to label this row.

b. Instead of typing in a formula to do an average, you can use the Function Wizard for fast and accurate calculation.

c. To use the Function Wizard, make the D20 cell active (we’ll be replicating something similar what we just did for Sum).

d. Now, we will want to calculate the average for this column. To do this, go back to the Auto Sum box but now click on the little arrow next to it. You will get a few options for formulas. Select Average.

e. Here the formula =AVERAGE(D7:D19) appears in the cell, which is the full column under the label. Verify this range and press Enter. The results now appear in the cell: 615,536 is our average estimated population per year.

$Formula = (\frac{Time 2 - Time 1}{Time 1}) \times 100 = (\frac{2019 rate - 2010 rate}{2010 rate}) \times 100$

$=(\frac{58.6 - 35.9}{35.9}) \times 100 = 63.3%$

$Formula = (\frac{Time 2 - Time 1}{Time 1}) \times 100 = (\frac{E16 - E7}{E7}) \times 100$

a. Click cell B21 to make it active and type % Change in order to label the row.

b. Select cell E21. Based on the percent change formula, we need to subtract the most recent date (2019, time 2) and the first date (2010, time 1) and then divide it from the first data point we have (2010, time 1). We also have to make sure to follow order of operations; if we do not use parentheses, Excel will give us a meaningless answer.

c. As such, we need to type in cell E21: =(E16-E7)/E7*100

d. Then hit enter. This calculates the percent change in the homicide rate from 2010 to 2019.

e. Here, this answer is 63.3%, thus we had a 63.3 % increase in homicide rate from 2010–2019.

• Open up a new excel worksheet. You will be entering a new set of data into excel in this lab.
• I will refer to a cell by its name in Excel. For example, Cell B4, is in column B and row 4.
• A range of cells selected together is defined by its “address,” which is the first cell and last cell in the rage of selected cells. So, if we were to select all the cells B4, B5, C4, and C5, the address would be “B4:C5” because it contains the cells from B4 to C5.

• Click cell B4 to make it the active cell.
• Type Homicide Rates in Baltimore, 2010-2019. Press Enter.
• Note: The title does not fit in the cell B4 and spreads across several columns. If a label does not fit in a cell, it will display the remaining text in the next cell(s) as long as they are empty. Otherwise, the label is truncated or cut off.
• Click cell B5 to make it the active cell and type Year
• Click cell C5 and type # of homicides
• Click cell D5 and type Estimated Population

• Notice that the labels truncated (or cut off). However, if you place the cursor on the cell, the full text appears in the formula bar. Don’t worry about the truncation for now, we will fix it later.

• Enter the data from the table provided below into the worksheet in the appropriate cells of the table. The placement and formatting may not be exactly like the picture below – that is ok. We will work on formatting the table later.

• Some of the population figures will be too large for the standard cell width, so they will be displayed with a series of number signs: ####. The data are there; it’s just that the column is too small to display all of the numbers.

• You may notice in the picture that the title is separated from the data. To do this, we will insert a row.
• To insert a row, move the cursor to row number 5. At the beginning of the row is the shaded box with the row number 5 inside. Click on the shaded box 5 and the whole row will become highlighted. Right click on the row and select Insert to add in a new row.

• Now we will format the cells so that they display the data better. First, we want to center the Year label and the data in those cells.
• Select the range beginning with the Year label down to the cell containing the last year of 2019 (cell range B6:B16). Under the Home menu at the top of the page, with the range highlighted, select Center from the Alignment group in order to center the text in this group.

• Now we want to make the titles for the homicides and population columns more readable and we want to see all of the population figures displayed in the cells.
• First, we will extend the width of these two columns. To do this, select the range of cells from C6:D16. Select Format from the Cells group at the top of the page, and then select Column Width. A box appears with the default width. Press the delete key to clear the box and type 22 to make it the new column width, then select OK.

• Next, we want to wrap the text of the labels for the homicide and population columns.

• To do this, click on the cell which has the label for the # of homicides column. Select the Wrap Text button from the Alignment group at the top of the page. Do the same for the label for Estimated Population.

• Now we will make a new column for our calculation of the Homicide Rates.
• In cell E6, type in Homicide Rate and with the cell active, select Wrap Text. Save your workbook again, and now we’ll move onto something slightly more complicated.