excel spreadsheet and need the explanation and answer to help me learn.
Requirements: I need to get it completely please have your answer true and fully right answer double check them,
CIS 2640 Midterm Exam Data: City of Baltimore Crime Data 2015 Source: https://www.kaggle.com/qusaybtoush1990/city-of-baltimore-crime-data Please 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 6 pm Evening: from 6 pm to 12 am 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%). Please use correct cell formatting to show the right form of data in your answers. You may see abnormalities in your data. Make sure to use the TRIM function when needed. Submit your file on Elearning,
