New Perspectives Excel 2019 | Module 11: SAM Project 1b Romano Collectibles CREATE ADVANCED PIVOTTABLES AND USE DATABASE FUNCTIONS GETTING STARTED ? Open the file NP_EX19_11b_ FirstLastName _1

Subject:MS ExcelPrice:20.01 Bought4

Share With

New Perspectives Excel 2019 | Module 11: SAM Project 1b

Romano Collectibles

CREATE ADVANCED PIVOTTABLES AND USE DATABASE FUNCTIONS

GETTING STARTED

?

Open the file

NP_EX19_11b_

FirstLastName

_1.xlsx

, available for download from the

SAM website.

?

Save the file as

NP_EX19_11b_

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

NP_EX19_11b_

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.

Anthony and Gina Romano have started to collect items such as sports memorabilia and

comic books, anticipating that their collections will appreciate in value. Anthony is using

an Excel workbook to track their collectible items and asks for your help in summarizing

data so he can evaluate the items gaining the most value. To do so, you will use

database functions and advanced PivotTable features.

Go to the

Collections

worksheet, which contains a table named Collectibles listing details

about the items the Romanos collect. In the range K3:N8, Anthony wants to summarize

item information.

Start by calculating the number of items in each category as follows:

a.

In cell L4, enter a formula using the

COUNTIF

function that counts the number of

comic books, checking that the Category column in the Collectibles table

(

Collectibles[Category]

) is equal to the value in cell

K4

.

b.

Fill the range L5:L8 with the formula in cell L4.

2.

In column M, Anthony wants to calculate the total value of the items in each category.

Determine the total values as follows:

a.

In cell M4, enter a formula using the

SUMIF

function that totals the value for comic

books, checking that the Category column in the Collectibles table

(

Collectibles[Category]

) is equal to the value in cell

K4

, and that the formula

totals all the current values (

Collectibles[Current Value]

).

b.

Fill the range M5:M8 with the formula in cell M4.

3.

In column N, Anthony wants to calculate the average value of the items in each category.

Determine the average values as follows:

a.

In cell N4, enter a formula using the

AVERAGEIF

function that averages the value

for comic books, checking that the Category column in the Collectibles table

(

Collectibles[Category]

) is equal to the value in cell

K4

, and that the formula

averages all the current values (

Collectibles[Current Value]

).

b.

Fill the range N5:N8 with the formula in cell N4.

4.

Anthony wants to identify the number of items that have a current value of more than

$200 and those that were acquired in 2020.

Create formulas that provide this information as follows:

a.

In cell L12, create a formula using the

DCOUNT

function to count the number of

items with current values of more than $200, using the data in the entire

Collectibles table (

Collectibles[#All]

) and counting the values in the column of

current values (

"Current Value"

) that are equal to the values in the range

K10:K11

.

b.

In cell L16, create a formula using the

DCOUNTA

function to count the number of

items acquired in 2020, using the data in the entire Collectibles table

(

Collectibles[#All]

) and counting the values in the column of acquired dates

(

"Acquired"

) that are equal to the values in the range

K14:K15

.

5.

Anthony also wants to calculate the total value of items in near mint condition and the

average of items in very good condition since he and Gina are likely to make the most

profit from these items.

Create formulas that provide this information as follows:

a.

In cell L20, create a formula using the

DSUM

function to calculate the total value

of the items in near mint condition, using the data in the entire Collectibles table

(

Collectibles[#All]

) and totaling the current values (

"Current Value"

) that are

equal to the values in the range

K18:K19

.

b.

In cell L24, create a formula using the

DAVERAGE

function to calculate the

average value of the items in very good condition, using the data in the entire

Collectibles table (

Collectibles[#All]

) and averaging the current values (

"Current

Value"

) that are equal to the values in the range

K22:K23

.

6.

Go to the

Value by Condition

worksheet. Anthony has created a PivotTable on this

worksheet to list the final current value of the collectible items by category, condition,

and year. He grouped the year data into two-year spans, but wants them listed as

separate years.

Ungroup the year data in the PivotTable.

7.

Anthony thinks the PivotTable looks crowded in its default Compact layout.

Change the report layout to show the PivotTable in

Tabular

Form

.

8.

Go to the

Items by Category

worksheet. Anthony created a PivotTable that lists each item

by category, and then counts the number of those items acquired each year. The data is

sorted in alphabetic order by category, but Anthony wants to sort the data by total

number of items. He also wants to focus on coins and comic books only.

Change the display of the PivotTable as follows:

a.

Sort the data in descending order by Grand Total.

b.

Apply a

Label Filter

that displays Category values that begin with

Co

.

9.

Go to the

Price and Value Comparison

worksheet. The PivotTable on this worksheet lists

the purchase prices and current values for each category of collectible item. Anthony

wants to include the number of items in each category in the second column of the

PivotTable.

Make this change for Anthony as follows:

a.

Add the

ID

field to the Values area of the PivotTable, making it the first field in the

Values area.

b.

Change the number format of the Count of ID amounts to

Number

with

0

decimal

places.

10.

Anthony wants to know the difference and the percentage of difference between the

purchase prices and the current values.

Provide this information for Anthony as follows:

a.

Insert a calculated field named

Difference

that subtracts the Purchase Price field

amount from the Current Value field amount.

b.

Insert another calculated field named

% Difference

that subtracts the Purchase

Price field amounts from the Current Value field amounts, and then divides the

result by the Purchase Price field amount.

c.

Change the number format of the Sum of % Difference amounts to

Percentage

with

2

decimal points.

11.

Anthony also wants to include the average purchase price for the items in each category

as the last column in the PivotTable.

Add this information to the PivotTable as follows:

a.

Add the

Purchase Price

field to the Values area of the PivotTable, making it the

last field in the Values area.

b.

Use the

Average

calculation to summarize the Purchase Price field data.

12.

Go to the

Purchases by Year

worksheet. The PivotTable on this worksheet lists the

purchase prices of the collectibles by year and category. Anthony wants to show the

names of each collectible item and display the information about the two categories with

the highest total purchase prices.

Provide this information for Anthony as follows:

a.

Expand the Category field to show the name of each item.

b.

Apply a

Value Filter

to the Category field that displays the top

2

items by the sum

of purchase price.

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: Collections Worksheet

Final Figure 2: Value by Condition Worksheet

Final Figure 3: Items by Category Worksheet

Final Figure 4: Price and Value Comparison Worksheet

Final Figure 5: Purchases by Year Worksheet