Shelly Cashman Excel 2019 | Module 6: SAM Project 1a | City of Honu Point -(Update 2025)

City of Honu Point
CREATE, SORT, AND QUERY TABLES

GETTING STARTED
• Open the file SC_EX19_6a_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SC_EX19_6a_FirstLastName_2.xlsx by changing the “1” to a “2”.
o If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file SC_EX19_6a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
o If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
PROJECT STEPS
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 2021.

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 (range A1:F12) as a table using Brown, Table Style Medium 7.
b. Use CompletedProjects as the name of the table.
c. Filter the table using a custom AutoFilter to display projects with a Funding amount 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 new row after the Lahua Street Apartment record in the CurrentProjects table for 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/2021
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 amount so that Dean can quickly identify the projects by funding amount.
4. Dean wants to list the projects that are in development in a separate part of the worksheet.

Use an advanced filter as follows to list these projects in a new range:
a. In cell H17, type Yes as the value to filter on in the criteria range.
b. Create an advanced filter using the CurrentProjects table (range A1:H12) as the List range.
c. Use the range A16:H17 as the Criteria range.
d. Copy the results to another location, starting in the range A19:H19.
5. As a contrast, Dean also wants to list the projects that are not in development.

In the CurrentProjects table, 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 2021. Dean suspects the ProposedProjects table has a duplicate record. Identify the duplicate as follows:
a. Clear the filter from the ProposedProjects table to display all the records.
b. In the range A2:A14, create a conditional formatting Highlight Cells Rule that displays cells with 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. In cell G1, type Fast Track as the column heading.
b. In cell G2, enter a formula using the AND function that includes structured references to display TRUE if a project has a [Project Type] of "Mixed Use" and a [Funding Type] of "Loan". Fill the range G3:G13 with the formula in cell G2 if Excel does not do so automatically.
8. Add a Total Row to the ProposedProjects table, which 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 less than 60 days to complete.
a. In the Number of Days column (range C2:C13), 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 Number type value greater than or equal to 120.
d. Display the yellow triangle icon in cells with a Number type value greater than or equal to 60.
e. Display the green circle icon in cells with a Number type value less than 60.

10. Dean also wants to compare the funding amounts visually. In the Funding column (range F2:F13), create a new Data Bars conditional formatting rule using Orange Gradient Fill data bars.
11. Wrap the text in cell J1 to display the complete contents of the cell.
12. Dean wants to summarize the number of projects proposed by the project type and calculate their funding amounts and average funding amounts.

Calculate this information for Dean as follows:
a. In cell J2, enter a formula using the COUNTIF function that counts the number of proposed Commercial projects, using ProposedProjects[Project Type] as the range and cell I2 as the criteria.
b. Fill the range J3:J5 with the formula in cell J2.
c. In cell K2, enter a formula using the SUMIF function that totals the funding for proposed Commercial projects, using ProposedProjects[Project Type] as the range, cell I2 as the criteria, and ProposedProjects[Funding] as the sum_range.
d. Fill the range K3:K5 with the formula in cell K2.
e. In cell L2, enter a formula using the AVERAGEIF function that averages the funding for proposed Commercial projects, using ProposedProjects[Project Type] as the range, cell I2 as the criteria, and ProposedProjects[Funding] as the average_range.
f. Fill the range L3:L5 with the formula in cell L2.
13. In the range I8:L12, Dean needs to insert a summary of the city development projects from the previous year. Insert this data as a table as follows:
a. Insert a table in the range I8:L12, specifying that the data has headers.
b. In the new table, enter the data shown in Table 2.
c. AutoFit the contents of columns I:L to display the complete cell contents.
d. Apply Brown, Table Style Medium 7 to the new table to match the formatting of the ProposedProjects table.

Table 2: Data for the New Table

Project Type Started Completed Funding
Commercial 5 3 45,500
Mixed Use 4 2 57,800
Public 4 3 33,750
Residential 3 3 41,325

14. Go to the Funding Totals worksheet, which lists all the current and proposed development projects. Dean wants to display the data by funding type and then list the projects by start date.

Sort the data in the table in ascending order first by funding type and then by start date.
15. Dean also wants to calculate subtotals for each funding type (Hint: You must complete all actions of this step correctly to receive full credit.):
a. Convert the table to a range.
b. Insert a subtotal at each change in the Funding Type value.
c. Use the Sum function to calculate the subtotals.
d. Add subtotals to the Funding values only.
e. Include a summary below the data.
f. Collapse the outline to display only the subtotals for each funding type and the grand total.
16. Go to the Lookup worksheet, which lists project details, including the ID code that staff in the Development Division use to refer to the projects. Dean wants to find a simple way to look up a project name based on its ID.

Create a formula that provides this information as follows:
a. In cell H3, begin to enter a formula using the VLOOKUP function.
b. Use the Project ID (cell H2) as the lookup value.
c. Use the Lookup table (range A2:E23) as the table_array.
d. Use the Project Name column (column 2) as the col_index_num.
e. Specify an exact match (FALSE) for the range_lookup.
17. Dean also wants to look up the start date of each project. Instead of using the VLOOKUP function, he suggests using the INDEX and MATCH functions, which are faster with large amounts of data.

Create a formula that provides the start date of a project as follows:
a. In cell H4, begin to enter a formula using the INDEX function.
b. Use the Lookup table (range A2:E23) as the array.
c. For the row_num argument, use the MATCH function.
d. Use the Project ID (cell H2) as the lookup_value for the MATCH function.
e. Use the ID column (range A2:A23) as the lookup_array for the MATCH function.
f. Specify an exact match (0) for the MATCH function.
g. Use the Start Date column (column 4) as the column_num for the INDEX function.
18. Dean also wants to identify the number of projects that have less than $5,000 of funding and calculate the average funding amount of commercial projects.

Create formulas that provide this information as follows:
a. In cell H8, create a formula using the DCOUNT function to count the number of projects with funding amounts less than $5,000, using the Lookup table (range A1:E23) as the database, "Funding" as the field, and the range G6:G7 as the criteria.
b. In cell H13, create a formula using the DAVERAGE function to average the funding amounts for Commercial projects, using the Lookup table (range A1:E23) as the database, "Funding" as the field, and the range G11:G12 as the criteria.
Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.
 
Final Figure 1: Completed Projects Worksheet

Final Figure 2: Current Projects Worksheet

Final Figure 3: Proposed Projects Worksheet

Final Figure 4: Funding Totals Worksheet

Final Figure 5: Lookup Worksheet