help 25 points
You have just been hired by the FBI to analyze a list of different mobster crimes to determine where the FBI
should prioritize their work. The previous analyst made some mistakes in creating formulas and you need to
identify and correct the mistakes. A spreadsheet is supplied for this assignment in Calc and Excel. Save the
spreadsheet as Mobsters_1_Your_Name. (Note: The mobster names may be real historical names, but the rest of
the data in this spreadsheet is fictional!)
Your job is to analyze the sheet, add some formulas, find the errors, and fix them. There is a lot of information on
this sheet, so let’s break it down, so it doesn’t seem so overwhelming.
• Columns A, B, C, and D all refer to the mobsters’ activity, and is considered one set of data. Remember
the need to select all the data in a set when sorting. This data contains the month the mobster committed
a crime, the name of the mobster, the crime they committed, and the amount of money involved.
• Columns F, G, and H use the information from Columns A, B, C, and D to calculate the total number of
crimes the mobster committed and the total amount of money involved per mobster.
• Columns J, K, and L give a list of the different types of crimes committed, the number of times they were
committed, and the total amount of money involved per crime.
Let’s get started analyzing this information.
1. Do not modify any data in columns A, B, C and D. You will modify formulas in columns G, H, K, and L.
2. In cell D167, use the SUM function to add the total amount of the payouts.
3. In cell D168, use the COUNT function to calculate the number of crimes committed.
4. Analyze and fix any formulas that are wrong. The cells highlighted yellow are your checker cells; if they
go to zero, then you have corrected the errors. The following cells have formulas you should check:
G3:G26, H3:H26, K3:K7, and L3:L7. (Hint: Remember the difference between relative and absolute
reference.)
5. After you have updated the spreadsheet, answer the questions that are in the reverse (white on black)
text in the spreadsheet. Simply type in the answers in the white cells.
Submit your spreadsheet, making sure it meets the guidelines below.
Points
Possible
Points
Earned
Cell D167 contains the correct formula and answer. 2
Cell D168 contains the correct formula and answer. 2
Checksum in yellow for Column G is zero by correcting the formulas. 3
Checksum in yellow for Column H is zero by correcting the formulas. 3
Checksum in yellow for Column K is zero by correcting the formulas. 3
Checksum in yellow for Column L is zero by correcting the formulas. 3
Which mobster made the most money is identified. 2
Which crime was the most frequently committed is identified. 2
Total Points 2