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

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

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.


Related Questions in business category


Disclaimer
The ready solutions purchased from Library are already used solutions. Please do not submit them directly as it may lead to plagiarism. Once paid, the solution file download link will be sent to your provided email. Please either use them for learning purpose or re-write them in your own language. In case if you haven't get the email, do let us know via chat support.