EXP ECH07 H3 - Greenwich Workshop 2.2

Project Description:

You are an analyst for an art gallery that is an authorized Greenwich Workshop fine art dealer (www.greenwichworkshop.com). Customers in your area are especially fond of James C. Christensens art. You prepared a list of artwork: art, type, edition size, release date, issue price, and estimated market value. You want to identify highly sought-after pieces based on age, percentage of value increase, and sold-out status. In addition, you want to perform an advanced filter and identify specific details from the filtered data.

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_LastFirst.	0.000
2	Make sure the Valuable worksheet is active and enter a nested logical function in the Comments column to display Highly Valuable if either condition is met: *The release date is on or before December 31, 1989, or *The sold-out status is Yes, the percentage increase in value is at least 500%, or the Edition Size was less than 400. Enter an empty text string if the two conditions are not met. Hint: You will need to nest two logical functions within the logical test argument. Use cell references to set all conditions. Copy the function down the column.	12.000
3	In cell N8, enter a database function to count the number of highly valuable pieces of artwork indicated in column K. Use the criteria in cells P2:P3 in your function.	12.000
4	In cell N9, enter a database function to average the estimated values (Est. Value) of highly valuable pieces of artwork indicated in column K. Use the criteria in cells P2:P3 in your function. In cell N10, enter a database function to total the estimate values (Est. Value) of the highly valuable pieces of artwork indicated in column K. Use the criteria in cells P2:P3 in your function.	12.000
5	Display the Database worksheet. Assign a range name called database to the range A14:J178.	10.000
6	Assign a range name called Criteria to the range A7:J9.	10.000
7	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).	5.000
8	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).	6.000
9	Create an advanced filter using the database list and Criteria range. Filter the records in place. Enter the appropriate database function in cell C2 in the Summary Statistics area to calculate the highest estimated value of the filtered records.	11.000
10	Apply Currency format and left-align the value in cell C2.	3.000
11	Enter a nested function using INDEX and MATCH to display the title from the Art column (in cell C3) and the release date (in cell C4) for highest estimated valued filtered artwork.	10.000
12	Left-align and format the date in cell C4 using the March-12 date format.	3.000
13	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.	6.000
14	Save and close the workbook, and submit the file as directed.	0.000
	Total Points	100.000