Shelly Cashman Excel 2019 | Module 6: SAM Project 1a City of Honu Point CREATE, SORT, AND QUERY TABLES GETTING STARTED ? Open the file SC_EX19_6a_ FirstLastName _1

Subject:MS ExcelPrice:19.99 Bought6

Share With

Shelly Cashman Excel 2019 | Module 6: SAM Project 1a

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”.

?

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.

?

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.

Add a row to the end of 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

Shelly Cashman Excel 2019 | Module 6: SAM Project 1a

 

Shelly Cashman Excel 2019 | Module 6: SAM Project 1a

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.

 

Shelly Cashman Excel 2019 | Module 6: SAM Project 1a

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.

 

Shelly Cashman Excel 2019 | Module 6: SAM Project 1a

Table 2: Data for the New Table

Project

Type

Starte

d

Complete

d

Funding

Commerci

al

5

3

45,500

Mixed Use

4

2

57,800

Public

4

3

33,750

Residentia

l

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.

 

Shelly Cashman Excel 2019 | Module 6: SAM Project 1a

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.

 

 

Shelly Cashman Excel 2019 | Module 6: SAM Project 1a

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