week2

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