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 Projectsworksheet, 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:
   1. Format the completed projects data (range A1:F12) as a table using Brown, Table Style Medium 7.
   2. Use CompletedProjectsas the name of the table.
   3. Filter the table using a custom AutoFilter to display projects with a Funding amount greater than $10,000.
2. Go to the Current Projectsworksheet, 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:
   1. In cell H17, type Yesas the value to filter on in the criteria range.
   2. Create an advanced filter using the CurrentProjects table (range A1:H12) as the List range.
   3. Use the range A16:H17 as the Criteria range.
   4. 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 Projectsworksheet, which lists projects that were proposed in 2021. Dean suspects the ProposedProjects table has a duplicate record. Identify the duplicate as follows:
   1. Clear the filter from the ProposedProjects table to display all the records.
   2. In the range A2:A14, create a conditional formatting Highlight Cells Rulethat displays cells with duplicate values using Light Red Fill and Dark Red Text.
   3. 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:
   1. In cell G1, type Fast Trackas the column heading.
   2. In cell G2, enter a formula using the ANDfunction 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 Rowto 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.
   1. In the Number of Days column (range C2:C13), create a new Icon Set conditional formatting rule using the 3 Signsicons.
   2. Reverse the icon order.
   3. Display the red diamond icon in cells with a Number type value greater than or equal to 120.
   4. Display the yellow triangle icon in cells with a Number type value greater than or equal to 60.
   5. 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 Filldata 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:
    1. In cell J2, enter a formula using the COUNTIFfunction that counts the number of proposed Commercial projects, using ProposedProjects[Project Type]as the range and cell I2as the criteria.
    2. Fill the range J3:J5 with the formula in cell J2.
    3. In cell K2, enter a formula using the SUMIFfunction that totals the funding for proposed Commercial projects, using ProposedProjects[Project Type]as the range, cell I2as the criteria, and ProposedProjects[Funding]as the sum_range.
    4. Fill the range K3:K5 with the formula in cell K2.
    5. 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 I2as the criteria, and ProposedProjects[Funding]as the average_range.
    6. 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:
    1. Insert a table in the range I8:L12, specifying that the data has headers.
    2. In the new table, enter the data shown in Table 2.
    3. AutoFit the contents of columns I:L to display the complete cell contents.
    4. Apply Brown, Table Style Medium 7to 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 Totalsworksheet, 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. 

AutoSave OFF OFF ROES SC_EX19_6a_NamLuong_2 - Saved to my Mac a Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X Calists MT (Body v 11 ~ Al A 25 Wrap Text General 5-E-D- 4 Paste BIU Av = = = Y Merge & Center v S% : 43 Insert Delete Conditional Format Cell Formatting as Table Styles Format Sorts Filter ideas Find & Select H1 fx A B C D E G H I L.M N 0 R S T LI V Project Name Start Date 1/16/21 1/20/21 2/4/21 2:18/21 3/3/21 3/19/21 4/2/21 4/23/21 5/9/21 5/12/21 5/18/21 Number of Days 360 60 300 120 280 65 240 180 30 30 40 Project Type Funding Type Funding Residential Grant $ 25,655 Public Lean 3 4.500 Mixed Use 540,003 Mixed Use Loan $68,500 Residencial Grant 325.350 Comercial Tax Credits 520,110 Residential Loan $ 17,550 Commercial Tax Credits $ 5,150 Public 5 5 3,100 Public Grant $ 1,770 Public Tax Credis $ 2.500 2 Hanu Avenue Apartments 3 Downtown Mall Redevelopment 4 Wessile Adult Community Cake 5 Research Center Paza Renovation 6 Safe Place 7 Marsa Circle Lana Street Triplex 9 Honu Point Medical Clinic Renovation 10 Kanak Mansion Improvement 11 Hanu Point High School 12 Maki Park Shared L's Path 13 14 15 16 19 18 19 20 21 22 2.3 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 12 Completed Projects Current Projects Proposed Projects Funding Totals Lookup + - + 100% AutoSave OFF OFF ROES SC_EX19_6a_NamLuong_2 - Saved to my Mac a Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X [ Calisto MT (Body v 11 ~ Al A ab Wrap Text General 5-E- ' , 4 Paste BIU y Av = = = - Merge & Center v Y S% Insert Delete Format Conditional Format Cell Formatting as Table Styles Sorte Filter ideas Find & Select - J1 fx E E F G H H I L. M . N 0 P Q Q R S T In Development Number of Days 720 21 24.1 360 420 Start Daty 6/2/21 6/13/21 7/12/21 7/28/21 9/1/21 9/201:21 10/3/21 10/9/21 11/1/21 11/18/21 Project Name 2 loku Lane Subdivision 3 Coos Struct Lol 4 Hallifax Brothers Expansion 5 Last Ridge Mixed Use 6 Lahus Seed Apartments 7 Kai Center Annexation & Oceanside Intel 9 Laki Trussical Building 10 Orion Lake Pavilion IL Listacic Survey 12 13 14 Project Name 15 16 17 Project Type Besidencial Residencial Mixed Use Mixed Use Resikat Commercial Commercial Core Public Publie Funding Type Funding Approved Loan $ $ 45,500 No Tax Credit S 2,000 Yes Tax Credit $ 2,5001 Yes Tax Credit $ 23,500 No Grani 9 3,010 Yes Loan $ 8,335 Yes Tax Credit $ 5,000 No Grani 5 20,000 Yes Crant s $ 9,700 Yes Grant $ 2,200 Yes No Yox No No Yes Yes No NO Yes Yes 60 325 180 10 Projects in Development Project Type Funding Type Number of Days Start Date Funding Approval? In Development? Project Name Start Date Number of Days Project Type Funding Type Funding Approved In Develupment? 18 19 20 21 22 23 24 25 27 28 29 30 SL 32 3.3 34 35 36 37 38 39 40 Completed Projects Current Projects Proposed Projects Funding Totals Lookup + - + 100% AutoSave IF OFF ROES SC_EX19_6a_NamLuong_2 - Saved to my Mac a Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X [ Calists MT (Body v 11 ~ Al A ab Wrap Text General 5-E- I AYO. 4 Paste BIU Av = = = Merge & Center v S% Delete Insert Y Sorts Format Ideas Conditional Format Cell Formatting as Table Styles - Find & Select Filter J1 fy A E F G I L. . N Q R S T C 720 120 1471 No Yes No Yes Yes Yes Yes 6/2/22 3/20/22 1/13/22 9/20/21 9/1/21 10/9/21 2/22/22 3/19/22 4/9/22 d/18/22 10/3/21 1/10/22 11/1/21 3/20/22 60 420 180 143 180 125 120 328 24 240 21 Residencial Mixed Use Commercia Commercia Residencial Commercial Public Resencial Commercial Mixed Use Comercial Public Publie Mixed Use Loan Grani Loun Loan Grani Grant Grant Tax Calif Tax Credit Loan Tax Credit Grant Grant Lun $ 45,500 5 4,000 $ 24,000 $ 8,555 $ 3,010 $ $ 12,500 5 15,000 s $ 9,50x 9,500 $ 50,000 5 5,000 $ 1,500 $ 9,700 5 2.500 H No No No Yes Yos No No NO No Yes No No Yes 1. No No No No No Yes No 10 Hoku Lane Subdivision 11 Home Point Center 12 Hanu Paint Sports Complex 13 Kai Center Annexation 14 Lahua Street Apurimmis 15 Laki Professional Building 16 Mahina Regional Park 17 Makna Road Multilicily 18 Manu Hill Drive Parking 19 Nalu Parks Village 20 Ocaside Hotel 21 Old Town Des en Pan 22 Orion Lake Pavilion 23 Sunset Heights Zoning 24 25 26 27 28 29 30 3L 32 33 34 35 36 37 38 39 10 41 02 44 45 16 47 48 09 50 51 52 53 Completed Projects Current Projects Proposed Projects Funding Totals Lookup + # EP - + 100% AutoSave OIF OFF ROES SC_EX19_6a_NamLuong_2 - Saved to my Mac a Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments X [G Calisto MT (Body v 11 ~ Al A ab Wrap Text General ' . 4 Paste BIU - Av = = = Y Insert Delete Y Merge & Center v Ideas Format Conditional Format Cell Formatting as Table Styles - Sorts Filter Find & Select J1 fx A B C F G . J L. M N Q R S T ID Project Name Project Type Start Date Funding Project Information 1 2 3 C329 Project ID Project Name Start Date Funding