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.
business
Description
|
New
Perspectives Excel 2019 | Module 11: SAM Project 1b |
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”.
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 NP_EX19_11b_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.
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.
