CJBS250/PAD 121 PROBLEM SET 2:

Basic Mathematics & Sorting and Filtering Data in Excel

This exercise is a follow-up to the lab session for “Data Analysis and Visualization in Excel II: Basic Math Operations, Sorting & Filtering”.

Problem Set Rules:

Each student should submit an individual problem set in Excel in Blackboard through the link, “Problem Set 2 Submission” in the Week 6 folder.

Discussing problem sets with other students is permitted. Copying from another person is not permitted.

Open the file that you created in Problem Set 1, titled “Problem Set 1-(Your Initial)” and make a copy of the file and name it as “Problem Set 2-(Your Initial)”; my file name is Problem Set 2-MK. We will use this tile in this problem set.

Go to the “Major Felony Offenses” sheet. Add a field titled “TOTAL” in Column O, next to “2011”. Calculate the total number of each of the seven major felony offences by using a math operation(s). You must create formulas in the cells, O5 through O11.

Add a field titled “AVERAGE” in Column P, next to “TOTAL”. Calculate the average number of each of the seven major felony offences by using a math operation(s). You must create formulas in the cells, P5 through P11.

Add a field titled “COUNT CHANGE” in Column Q, next to “AVERAGE”. Calculate the count change in each of the seven major felony offences by using a math operation(s). You must create formulas in the cells, Q5 through Q11.

Hint: Count Change = Final Value – Initial Value

Add a field titled “PERCENT CHANGE” in Column R, next to “COUNT CHANGE”. Calculate the percentage change in each of the seven major felony offences by using a math operation(s). You must create in the cells, R5 through R11, formulas that return a decimal and convert that to percentage by adding a “%” sign.

Hint: Percent Change = ((Final Value-Initial Value)/(Initial Value)) × 100 (Note: In order to get the right number after adding a % sign, delete “× 100” from the formula.)

Add a text box below or next to the table you just edited in Q1-4 in the “Major Felony Offenses” sheet as show on page 3, and answer Q5-7. Write the question numbers in the text box before continuing with your answers.

What is the number one crime in NYC among the seven major crimes between 2000 and 2011? Why?

What is the most threatening crime(s) in NYC among the seven major crimes between 2000 and 2011, which would need a special attention of NYPD? Why?

Adam Cracks, a journalist who works on local papers in NYC recently argues in his article that the NYC government should hire more police officials to make the city safer. To support this idea, he asserts that major crimes taking place in the city, including murder, rape, robbery, and burglary, have sharply increased since 2000 through 2011. How would you respond to his argument?

Go to the “MFO-Transpose” sheet. Add a text box into the sheet in order to answer Q9-10. Write the question numbers in the text box before continuing with your answers.

In Column J, create the field titled “TOTAL SEVEN MAJOR FELONY OFFENSES” and calculate it for each year by using a math operation(s). You must create formulas in the cells, J5 through J16.

Find the years in which > 562 murders and > 1,683 rapes were committed in NYC by using the filtering function in Excel.

Find the top 10 years of total seven major felony offenses by using the filtering function in Excel.

Save the excel file as it is so that I can see the filter function that you used in Q1 and submit it in Blackboard.

Figure 1. An Example of Text Box Inserted in an Excel Spreadsheet