Answered step by step
Verified Expert Solution
Question
1 Approved Answer
lease read the following questions and answer them in the excel file. 1- Use TIME/DATE functions to identify the month and hour of crime in
lease read the following questions and answer them in the excel file. 1- Use TIME/DATE functions to identify the month and hour of crime in columns "Crime Month" and "Crime Hour" (5\%). 2- Use text functions and split the "District / Neighbourhood" column into "District" and "Neighbourhood" columns (15\%). 3- Use IF function to mark violent inside crimes (Inside crimes with firearms) with "YES" in the "Violent Inside Crimes" column. For other crimes, put NA in the column. You need to pay attention that the content in the "Inside/Outside" column is not consistent. For instance, you can see "I, O, Inside, and Outside" in the column (10\%) 4- Use IF function to create three categories for crime time (Midnight, Daylight, Evening) in the column "Crime Time Category" (10\%) Midnight: From 12 am to 6 am Daylight: from 6 am to 6pm Evening: from 6pm to 12am 5- Use INDEX and MATCH functions to identify the information about the crime that happened in the identified addresses (15%). 6- Use VLOOKUP and IF functions to identify if the crime in the identified address was a violent inside crime or not (10%). 7- Use pivot table and answer the following questions (35\%): - What is the most dangerous district? - What is the month with the highest number of crimes? - What is the time category with the highest number of crimes? - What are the top three priorities (Neighbourhoods) for allocating police cars during midnight? - What is the safest neighborhood? - What is the most commonly used weapon in "ROBBERY - CARJACKING"? - Which location has the highest number of crimes? 8- Use various data analysis and visualization techniques and create a dashboard to help decision-makers reduce crimes. Write one paragraph explaining how the visualization may help decision-makers (Bonus points up to 20% )
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started