In this assignment, you will become familiar with preparing descriptive statistics Microsoft Excel using software. Microsoft Excel is a popular spreadsheet tool with the ca
nstructions
Uniform Crime Report
In this assignment, you will become familiar with preparing descriptive statistics Microsoft Excel using software. Microsoft Excel is a popular spreadsheet tool with the capability of analyzing data quickly and easily. In this course, we will use data sets pulled from a variety of reliable resources. We will input these data sets into the Microsoft Excel software and create analysis reports. For your convenience, we will provide the data set and a step-by-step instructions guide (the links are provided in the Project Resources below) to help you complete the requirements of the assignment.
In this week’s readings, you learned that descriptive statistics put quantitative descriptions into a manageable form. This useful analysis involves reducing large amounts of data to manageable summaries or coding.
Install Data Analysis ToolPak in Microsoft Excel:
Install Analysis ToolPak
Microsoft Excel tutorials:
If you would like help in learning how to use Microsoft Excel, search YouTube for video tutorials. These tutorials will help you get started with the Microsoft Excel software, and tutorials can be found for all levels of expertise.
Project Resources: (links provided below)
There are two resources needed to complete this assignment.
- The Step-by-Step Instructions
- The Data Set: Hypothetical City's Uniform Required Crimes Report (2008–2012)
Tasks:
In this assignment, you will use Uniform Crime Report (UCR) statistics to analyze interval variables for violent crimes over a 5-year period. The information needed to complete this assignment is conveniently accessed by clicking the links in the Project Resources below.
- To get started, click the image below to access the Step-by-Step Instructions for using the data set in the Microsoft Excel software.
- Use the data set provided by clicking the link below for the Uniform Crime Report data. Copy and paste this data into the Microsoft Excel software.
- Use this information to create descriptive statistics to analyze the types of crimes that are committed and the variance between crimes from 2008 and 2012.
- Create and recode the variables to produce a table and a chart illustrating the variance between crimes from 2008 and 2012.
- Display your findings by creating appropriate tables and graphs using the data you downloaded to a Microsoft Excel spreadsheet.
- Present the Microsoft Excel spreadsheet as a table in a Microsoft Word document; be sure to include the tables and graphs you created to support your analysis of the data and state your conclusions about the variance in the types of violent crimes between 2008 and 2012.
Install Analysis ToolPak
1. Open Excel, bring up a blank workbook, and click File.
2. On the left-hand side menu, all the way to the bottom, click Options 3. Click Add-ins
4. Click Analysis ToolPak
5. Click OK
Export Summary
| This document was exported from Numbers. Each table was converted to an Excel worksheet. All other objects on each Numbers sheet were placed on separate worksheets. Please be aware that formula calculations may differ in Excel. | |||
| Numbers Sheet Name | Numbers Table Name | Excel Worksheet Name | |
| Sheet1 | |||
| Table 1 | Sheet1 | ||
| Sheet2 | |||
| Table 1 | Sheet2 | ||
| Sheet3 | |||
| Table 1 | Sheet3 |
Sheet1
| Year | 2008 | 2009 | 2010 | 2011 | 2012 | |
| Murder/Non-Negligent Manslaughter | 294 | 287 | 269 | 198 | 217 | |
| Forcible Rape | 750 | 823 | 712 | 771 | 665 | |
| Robbery | 10603 | 11367 | 9449 | 8054 | 9385 | |
| Aggravated Assault | 13132 | 13116 | 12061 | 11869 | 11343 | |
&"Helvetica Neue,Regular"&12&K000000&P
Sheet2
&"Helvetica Neue,Regular"&12&K000000&P
Sheet3
&"Helvetica Neue,Regular"&12&K000000&P
,
MCJ5100 WEEK 2 PROJECT STEP-BY-STEP INSTRUCTIONS
Week 2 Project: Working with Microsoft Excel – Descriptive Statistics Companion to Week 2 Project/Step by Step Instructions using Microsoft Excel Summary of Instructions: for this Project you will use the data set provided on the Project page. Download the Microsoft Excel file and save to your hard drive. Variable names are listed Across Row 1, and your data for each variable is located in the columns under the variable names. FORMAT YOUR DATA FILE:
1. Change the formatting of your variable names so they fit the cells and are easily read. It is helpful to give them shorter names when possible.
a. Highlight the data in Row 1, the variable names. b. Click Wrap Text c. Change font to bold. d. Change column width to fit the data.
RUN DESCRIPTIVE STATISTICS:
1. Click Data 2. Click Data Analysis
3. Select Descriptive Statistics, Click OK
4. In the Input Range box, enter the range of cells where your data are located, including variable names.
a. Select Grouped By: Columns b. Check the box for Labels in first row c. Under Output options select Output Range. Place your cursor
in the box and select a cell in your worksheet where you would like your output to begin.
d. Check the box for Summary statistics. e. Click OK.
5. On your spreadsheet, you will see this:
6. Format your Output.
a. The descriptive statistics for each variable are listed down two columns, with the variable name identified and all variables placed next to each other. But the variable names are long, run together and do not neatly identify which columns are addressed.
b. So, data need to be formatted so they are readable.
c. Adjust variable names, widen columns as necessary, and reformat your numbers, changing from General to Number. Number will give you two decimal places. You may do this selectively, cell by cell or simply highlight the entire table and allow all numbers two decimal places.
d. Final formatted table should look similar to this:
7. If you wish, you can take this a step further, eliminating (in this example) columns I, K, and M, as they are redundant, repeating information from column G.
8. Notice in the example above no descriptive statistics were computed for Year. Year is measured on a nominal scale. It is categorical, and it does not make sense to do math with a categorical variable.
Student Response – Analysis of Data Write your initial analysis of these violent crimes looking at the data set year by year to draw your hypothesis. Then use the output from this Microsoft Excel exercise to compare each crime using the Mean, Standard Deviation, Minimum, and Maximum data. Check your readings for the week for more information on how to apply this data in your analysis.
In addition to your written analysis support your findings by creating pie charts illustrating the variances in crimes 2008 and 2012. Producing a Pie Chart
1. Select the data to be included in the Pie chart.
a. To select both the labels (year) and data (Robbery), first select the data under year, then hold Ctrl and select the data under Robbery.
2. Insert the Chart
a. Click Insert, click Recommended Charts
b. Select Pie Chart, click OK
a. Format your Pie Chart
1) Click on Chart Title, rename, and move it where you would like it on the chart.
2) Click on the Chart Elements button (the +) to add labels and percentages
a. Check the box next to Data Labels b. Click on the arrow to the right of Data Labels, and select
Data Callout to add year and percentages
The graphs menu lists many chart options. Now that you know how to find the list of Graphs, try some other types of graphs: bar charts, histograms, line plots, etc., using different variables from your table of data.
2008 22%
2009 23%
2010 19%
2011 17%
2012 19%
Robbery
2008 2009 2010 2011 2012
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
