njksvgkjsnbvw

Assignment 2 McCormack CRIM.3950.031

Provided is a data set that shows the number of arrests from a group of male juveniles in DYS

custody, and group of male adults in DOC custody. Calculate the values below – provide evidence

of the steps you used (writing/typing out):

Juveniles: 05 05 06 07 08 10 06 06 08 08 07 03

Mean =

Range =

Variance =

Standard Deviation =

Adults: 12 09 08 03 02 06 10 07 11 14 04 05 10 09 05 02

Mean =

Range =

Variance =

Standard Deviation =

Assignment 2 McCormack CRIM.3950.031

Open the Excel data file in the Week 8 folder.

Your spreadsheet should like this:

The values we will calculate in the next two assignments are the measures of central tendency,

measures of variability/dispersion, quartiles, and measures of normality - those of which may be

new to many of you. The utility of all of these values lie in their ability to describe a distribution

(a set of values for a variable). We can tell a lot about a variable and its values based just on these

few descriptive measures. Based on the raw data, we can get a general idea about differences

between the values such as Thursday being the day in which most crime in Boston occurs – did

that surprise you?

Assignment 2 McCormack CRIM.3950.031

To get a better idea of the distribution of crime, we will calculate the aforementioned measures

using the following Excel functions. They are fairly simple, and require just a few characters of

input. See below for the commands, and instructions, to calculate these values in Excel.

Measures of Central Tendency

Mode =MODE.SNGL1(array of values2)

Median =MEDIAN(array of values)

Mean =AVERAGE(array of values)

Don’t include the superscripts - signaling a footnote - in the function! I placed

them here to explain the terms/functions.

Using the commands above, we will calculate the values for our first table “All Crime: Offense

Type”. In order to do so, we need to use what is referred to as cell referencing. Essentially, we will

be telling Excel which cells to draw values from in performing its calculations. It sounds more

complicated that it is.

In our Excel spreadsheet we have several tables currently. Below the first table is another, large

table showing a variety of crime totals in Boston from the years 1985 through 2014. Type in the

following labels: mode (in cell A46), median (in cell A47), and mean (in cell A48). Your

spreadsheet should look something like this:

Now, let’s calculate the aforementioned values for this table. Move your cursor and select (click)

cell B46. In the cell, type the command seen below, and reference the cells we want to examine:

=MODE.SNGL(b15:b44)

When you press enter/return, you will likely come up with “#N/A” in cell B46, indicating an error.

Why is that? That is because Excel can’t provide the value of multiple modes. Here, that is the

1 For Excel 2007 and earlier, use “=MODE(array of values) 2 Array of values refers to the ‘group’ of values, or data, that you want to analyze with the command.

Assignment 2 McCormack CRIM.3950.031

case. Each year probably has a different value – though this would occur in any instance of multiple

modes. In cell B47, type the command seen below, and reference the cells we want to examine:

=MEDIAN(b15:b44)

When you press enter/return you should see the value 35,788. That is the median number of crimes

annually reported in Boston from 1985 to 2014.

Finally, in cell B48, type the command seen below, and reference the cells we want to examine:

=AVERAGE(b15:b44)

When you press enter/return you should see the value 43,069. That is the mean number of crimes

annually reported in Boston from 1985 to 2014.

These values can be displayed in a simple table:

All Reported Crimes

Annual Number in Boston 1985-2014

Mode N/A (multimodal)

Median 35,788

Mean 43,069

Measures of Central Tendency

Calculate the measures of central tendency for the same 3 offenses you created charts for in

Assignment 1. Create a similar table for those same 3 offenses.

Copy/paste them into (or create them in) a Word document (.doc or .docx) which will be

submitted.

Beneath each table (3 you picked and created), write a 100-word paragraph describing the

measures of central tendency AND long-term trends as seen in the Excel 1985-2014 table.