Advanced Graphing and Charting

Advanced Graphing and Charting >> Introduction to Data Analysis Using Excel

1. Download the spreadsheet “chicagocrimedata.xlsx” below.  Use the data in the “Question 1 – get data” tab for the remainder of the assignment.
chicagocrimedata.xlsxFill in the tab, “Question 1 – get data” using the VLOOKUP formula. What is the value in cell C200?  

79

2. Use a pivot chart to create a bar plot of the average assaults by day of week. Which days of week has the fewest assaults on average?

• Weekdays (Monday – Friday)
• Weekends (Saturday and Sunday)
• Sunday and Monday
• Friday and Thursday

3. Modify the pivot plot to plot the average temperatures by day of week. What does it look like?

Check the numbers & match with images

• The frequency on the y-axis varies from 60-80
• The frequency on the y-axis varies from 51.4-53.2
• The frequency on the y-axis varies from 56-63
• The frequency on the y-axis varies from 2900-3300

4. Create a scatter plot of Assault (Y axis) vs. Temperature (X axis). What does it look like?

Check the numbers & match with images

• The chart shows a scattered plot with a bell shaped trend
• The scatter plot is upward sloping with closely aligned data points ranging from 28~110 along x-axis
• The scatter plot is upward sloping with data points ranging from 700~1600 aling x-axis
• The scatter plot is upward sloping with data points ranging from 10~100 along x-axis

5. What is the possible relationship between crime and temperature?

• Crime increases as temperature rises.
• Crime decreases when temperature rises.
• Temperature and crime are unrelated.
• Temperature increases as crime rises.

6. Find the minimum and maximum number of assaults, and create 10 evenly spaced bins that cover the data on assaults. What is the approximate bin width?

• 8
• 10
• 15
• 2

7. Create a histogram using the assaults data and 10 bins. What can you conclude based on the histogram?

• The distribution of assaults is skewed to the right.
• The distribution of assaults is symmetric.
• 100 assaults is about a typical day in Chicago.
• Crime is highest midweek?