Long Beach City College Week 1 Crime Statistics Spreadsheet Project

• February 05, 2022/

Description

Spreadsheets are a critical tool for the management, evaluation, and display of data. In fact, spreadsheets can be used for most statistical functions that you’ll be asked to master in this class. One of the core learning objectives in this class is to develop basic data presentation and analysis skills using Microsoft Excel. Most of the assignments and examinations will require use of functions in Excel, and our data visualization exercises will utilize Excel graphing applications. Because skills in Excel inevitably vary, we are asking all class members to complete a simple Excel exercise that will gauge skill levels coming into the class. This will help individual students identify where practice is necessary or additional resource needs might be needed.

Please complete the exercise in the PPD 504 Week 1 Excel Assignment: Crime Statistics Spreadsheet, which shows crimes reported in the United States by state (2017 data). Note that the data are broken down geographically (cities [metropolitan and outside metropolitan] and nonmetropolitan counties) and by type of crime (violent and property). Note that the crime data are included for each state as a rate per 100,000 inhabitants. This allows us to look at data corrected for population, thus allowing for state-to-state comparisons. WARNING: PLEASE BE AWARE THAT THIS TABLE CONTAINS DATA INCLUDING VIOLENT CRIME STATISTICS THAT STUDENTS MAY FIND DISTURBING.

For this exercise we are going to populate the spreadsheet using common functions and then create stacked column graphs, which are excellent for showing parts that make up a whole. For this exercise, we are going to look at crime data from Colorado and Oregon.

1) Violent crime and property crime numbers are made up of smaller subsets of crime numbers, as recorded in the attached spreadsheet. Your first task will be to calculate the total number of violent crimes and property crimes for each area in each state using the “SUM()” function in Excel. Note that you will be filling in the yellow highlighted cells for this portion of the exercise. Don’t make changes to the nonhighlighted cells.

2) Next, you will calculate the number of violent crimes and property crimes (for both the total and each subset) using the “SUM()” function. You will be filling in the green highlighted cells in Rows 11 and 19.

3) Finally, you will calculate the crime rate for violent crime and property crime (for both the total and each subset) using standard Excel functions. This done using the equation: 100,000*(crime number/total population). We multiply by 100,000 because the data are reported in rate per 100,000 inhabitants. You will be filling in the green highlighted cells in Rows 12 and 20.

NOTE: FOR DEMONSTRATION PURPOSES COLORADO VIOLENT CRIME DATA HAVE BEEN COMPLETED. USE THE RELEVANT CELLS AS A GUIDE.

4) Next we are going to make some charts. Create a stacked-column chart (this is under the 2D Column option under the “Insert” tab in Excel) that shows

a. Total violent crime numbers for each state by component (Columns F, G, H, and I) for all areas combined (Rows 11 and 19). Paste your chart below this paragraph. When finished you should have one chart showing violent crime for Colorado and Oregon. An example is included for Colorado violent crime in the spreadsheet. You will have to add Oregon.

b. Total property crime numbers for each state by component (Columns K, L, and M) for all areas combined (Rows 12 and 20). Paste your chart below this paragraph. When finished you should have one chart showing property crime for Colorado and Oregon. Key things to include for all graphs: a) title for your graph, b) label for each axis, c) a legend, and d) a footnote indicating what components made up each stacked-column graph. Insert the charts below this paragraph.

5) Next, you are going to compare crime rates between your two states. Create a bar chart (Insert tab, select the down arrows next to the bar/column charts) for the total violent crime rate for each state you selected. Do the same for the total property crime rate for each state. You should have two charts for this portion of the exercise (one for violent crime and one for property crime). Insert the charts below this paragraph.

Finally, answer the following questions based on the charts you have created. Note that if you are not comfortable with Excel, we encourage you to participate in the optional training available on USC’s Lynda website. This week, please focus on Excel 2016: Introduction to Formulas and Functions, Chapter 1: Introducing Excel 2016 Functions and Formulas. Please work through Parts 1 and 2 of this training as needed. Please enter an answer for each question below.

Question 1: What is the total number of violent crimes reported for Colorado and Oregon in 2017? What geographic area had the highest number of violent crimes for each state? What type of violent crime was the biggest contributor to the total number of violent crimes for each state? Repeat this for property crimes.

Question 2: Based on the charts and table you created above, which state has a higher violent crime rateoverall? Which state has a higher property crime rate overall? Which state has a higher aggravated assault rate? What about motor vehicle theft?

Question 3: Provide a brief self-assessment regarding how comfortable you feel you are with Excel, specific to this exercise, and indicate if you could use additional assistance.