Shelly Cashman Excel 365/2021 | Module 6: SAM Critical Thinking Project 1c #shelly #module6
If you directly want to get the project from us then contact us on Whatsapp. Click on link below👇👇
wa.me/message/PPXGSJS2TLWUA1
Whatsapp Contact Number:
+91 8114420233
Email ID:
excelprojectshelp@gmail.com
1. Dean Yamaguchi is a development officer for the city of Honu Point in Hawaii. Dean is analyzing city development projects that have been completed, are in progress, and have been proposed. He asks for your help in using Excel tables to complete the analysis.
Go to the Completed Projects worksheet, which lists the development projects that were completed in Honu Point in 2024.
Create a table as follows so that Dean can summarize and filter the data and display projects with the highest funding amounts:
a. Format the completed projects data as a table using the same style as the table on the Current Projects worksheet.
b. Use CompletedProjects as the name of the table.
c. Autofilter the table to display projects with a Funding value greater than $10,000.
2. Go to the Current Projects worksheet, which contains the CurrentProjects table listing city development projects that are under review or in development. The city recently received a proposal for a new development project.
Insert a blank row at the end of the CurrentProjects table. Add a new record containing the data shown in Table 1.
Table 1: New Record for the CurrentProjects Table
Project Name Orchid Drive Zoning
Start Date 11/20/2024
Number of Days 30
Project Type Public
Funding Type Loan
Funding $2,000
Approved? No
In Development? No
3. Sort the CurrentProjects table in ascending order by Funding values so that Dean can quickly identify the projects by their funding amount.
4. In a separate part of the worksheet, Dean wants to list the projects that are in development.
Use an advanced filter as follows to list these projects in a new range:
a. In the Projects in Development section, type Yes under the "In Development?" heading as the value to filter on in the criteria range.
b. Create an advanced filter using the CurrentProjects table as the List range.
c. Use the headings and data row below the "Projects in Development" heading as the criteria range.
d. Copy the results to another location, starting with the second set of headings below the "Projects in Development" heading.
5. As a contrast, Dean also wants to list the projects that are not in development. Filter the CurrentProjects table in place, and then use the filter arrows to limit the table display to projects that are not in development.
6. Go to the Proposed Projects worksheet, which lists projects that were proposed in 2024. Dean suspects the ProposedProjects table has a duplicate record. Identify the duplicate as follows:
a. Display all the records in the ProposedProjects table.
b. In the Project Name column of the ProposedProjects table, use conditional formatting to highlight duplicate values using Light Red Fill and Dark Red Text.
c. Delete the second instance of the duplicate record so that you can summarize the data accurately.
7. The city of Honu Point wants to fast-track mixed-use development projects that use loans for funding. Add a column to the ProposedProjects table, and determine which projects meet the criteria as follows:
a. Type Fast Track as the column heading to the right of the ProposedProjects table to add the new column. [Hint: Use the existing blank column as the new column. Do not insert a column.]
b. In the cell beneath the heading, use the AND function that includes structured references to display "TRUE" if a project has both the Mixed Use project type and the Loan funding type. [Hint: To avoid an #SPILL error, enable implicit intersection by placing the This Row specifier (@ character) in front of field names.]
c. Fill the rest of the column with the new formula if Excel does not do so automatically.
8. Add a total row to the ProposedProjects table, that automatically counts the number of Fast Track values. Using the total row, display the sum of the funding amounts.
9. Dean asks you to identify the projects that require 120 days or more to complete, those that require 60 days or more to complete, and those that require fewer than 60 days to complete.
a. In the Number of Days column, create a new Icon Set conditional formatting rule using the 3 Signs icons.
b. Reverse the icon order.
c. Display the red diamond icon in cells with a numerical value greater than or equal to 120.
d. Display the yellow triangle icon in cells with a numerical value greater than or equal to 60.
e. Display the green circle icon in cells with a numerical value less than 60.