Instructions_NP_EX19_11b

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. Start by clearing the filter from the Category field. 2. Instead of seeing the data sorted by purchase price, Anthony and Gina would like to see the data by year. Sort the table data by the Acquired field from smallest to largest. 3. 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.

New Perspectives Excel 2019 | Module 11: SAM Project 1b 4. 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. 5. 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. 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.

New Perspectives Excel 2019 | Module 11: SAM Project 1b Final Figure 1: Collections Worksheet Final Figure 2: Value by Condition Worksheet

New Perspectives Excel 2019 | Module 11: SAM Project 1b Final Figure 3: Items by Category Worksheet Final Figure 4: Price and Value Comparison Worksheet