Instructions: For the purpose of grading the project you are required to perform the following tasks:
Step
Instructions
Points Possible
1 Open exploring_e07_grader_h3_Art. xlsx and save it as exploring_e07_grader_h3_Art_LastFir st. 0.000
2 In cell G2 in the Valuable worksheet, enter the YEARFRAC function to calculate the number of years from the release date and the date in cell Q2. Use relative and mixed references correctly. Format the value with Comma Style with one decimal place. Copy the function to the range G3:G40. 10.000
3 In cell L2 in the Valuable worksheet, enter a nested logical function to display Highly Valuable if either condition is met:
*The release date is on or before December 31, 1994, or
*The sold-out status is Yes, the percentage increase in value is at least 500%, and the Edition Size was less than 700.
Enter an empty text string if the two conditions are not met.
You need to nest two logical functions within the logical test argument. Use cell references to set all conditions. Copy the function from cell L2 to the range L3:L40. 10.000
4 In cell O10 in the Valuable worksheet, enter a database function to count the number of art pieces where the comment is Highly Valuable. The criteria range is located in the range N6:N7. 6.000
5 In cell O11 in the Valuable worksheet, enter a database function to calculate the average Est. Value of highly valuable pieces of artwork indicated in column J. The criteria range is located in the range N6:N7. 6.000
6 In cell O12 in the Valuable worksheet, enter a database function to total the estimate values (Est. Value) of the highly valuable pieces of artwork indicated in column L. The criteria range is located in the range N6:N7. 6.000
7 Display the Database worksheet. Assign a range name called database to the range A14:J178. 6.000
8 In the Database worksheet, assign a range name called Criteria to the range A7:J9. 6.000
9 Create column labels for the Criteria range (in Row 7) by copying the labels from Row 14, and then replace Edition Size with a second Release Date column label (in E7). 6.000
10 Set the following conditions in the Criteria range:
*Sold-out Limited Edition Canvases (Yes in cell H8) released after 1/1/2000 (in E8) and before 12/31/2003 (in F8).
*Sold-out Limited Edition Prints (Yes in cell H9) released after 1/1/2000 (in E9) and before 12/31/2003 (in F9). 10.000
11 Create an advanced filter using the database list and criteria range. Filter the records in place. In cell C2, enter the appropriate database function to calculate the highest estimated value of the filtered records. 11.000
12 Apply Currency format and left-align the value in cell C2. 2.000
13 In cell C3, enter a nested function using INDEX and MATCH to display the title from the Art column and in cell C4, enter a nested function using INDEX and MATCH to display the release date for highest estimated valued filtered artwork. 14.000
14 Left-align and format the date in cell C4 using the March-12 date format. 3.000
15 Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side on each worksheet. 4.000
16 Save and close the workbook, and submit the file as directed. 0.000
Total Points 100.000