Exploring E CH4 H3 Artwork 2.0
Question # 00029086
Posted By:
Updated on: 10/24/2014 02:41 AM Due on: 10/24/2014
Project Description:
You work for a gallery that is an authorized Greenwich Workshop fine art dealer (www.greenwichworkshop.com).
Customers in your area are especially fond of James C. Christensen’s art. Although customers can visit the Web site to see images and details about his work, they have requested a list of all his artwork. Your assistant prepared a list of artwork: art, type, edition size, release date, and issue price. In addition, you included a column to identify what pieces are sold out at the publisher, indicating the rare, hardtoobtain artwork that is available on the secondary market. You now want to convert the data to a table so that you can provide information to your customers.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Points
Possible
Instructions
1 Start Excel. Download and open the file named e04_grader_H3.xlsx.
0.000
2 Convert the data to a table and apply Table Style Medium 5.
5.000
3 Sort the table by the Type in alphabetical order, and then by Release Date from newest to oldest.
5.000
4 Type Total Original Value in cell G1. In cell G2, enter a structured formula: If Edition Size is greater
than 0, then multiply Edition Size by Issue Price; otherwise display an empty cell using "". Format the
range G2:G105 with Accounting Number Format with zero decimal places.
12.000
5 Delete the two rows containing Poster art types.
7.000
6 Turn on the table total row. Delete the total in cell G104. In cells C104 and E104, select the function that
shows the largest edition size and the most expensive issue price. Type Largest Edition Size and
Highest Issue Price in cell A104.
9.000
7 Create a custom conditional format for the Issue Price range E2:E103, with these specifications:
•4 Traffic Lights icon set (Black, Red, Yellow, Green)
•Red icon when the number is greater than 1000 Number
•Yellow icon when the number is less than or equal to 1000 and greater than 500 Number
•Green icon when the number is less than or equal to 500 and greater than 250 Number
•Black icon when the number is less than or equal to 250.
10.000
8 Filter the table by the Red Traffic Light conditional formatting icon.
0.000
9 Answer the first question below the worksheet data based on the filtered table. Type your answer in cell
B107.
5.000
Updated: 04/18/2014
1
Current_Instruction.docx
Step
Points
Possible
Instructions
10 Answer the second question below the worksheet data based on the filtered table. Type your answer in
cell B108.
5.000
11 Answer the third question below the worksheet data based on the filtered table. Type your answer in cell
B109.
5.000
12 Answer the fourth question below the worksheet data based on the filtered table. Type your answer in
cell B110.
5.000
13 Answer the fifth question below the worksheet data based on the filtered table. Type your answer in cell
B111.
5.000
14 Set the column width for column A to 46.0. Select Landscape orientation, and set the scaling to fit the
data to 1 page.
8.000
15 Set 1-inch top and bottom margins. Set 0.3-inch left and right margins.
6.000
16 For column B, set the column width to 30. For columns C and D, set 8.5 column widths and apply Wrap
Text.
7.000
17 Insert a footer with the text Exploring Series on the left side, the sheet name code in the center, and the
file name code on the right side.
6.000
18 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
0.000
Total Points
100.000
You work for a gallery that is an authorized Greenwich Workshop fine art dealer (www.greenwichworkshop.com).
Customers in your area are especially fond of James C. Christensen’s art. Although customers can visit the Web site to see images and details about his work, they have requested a list of all his artwork. Your assistant prepared a list of artwork: art, type, edition size, release date, and issue price. In addition, you included a column to identify what pieces are sold out at the publisher, indicating the rare, hardtoobtain artwork that is available on the secondary market. You now want to convert the data to a table so that you can provide information to your customers.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Points
Possible
Instructions
1 Start Excel. Download and open the file named e04_grader_H3.xlsx.
0.000
2 Convert the data to a table and apply Table Style Medium 5.
5.000
3 Sort the table by the Type in alphabetical order, and then by Release Date from newest to oldest.
5.000
4 Type Total Original Value in cell G1. In cell G2, enter a structured formula: If Edition Size is greater
than 0, then multiply Edition Size by Issue Price; otherwise display an empty cell using "". Format the
range G2:G105 with Accounting Number Format with zero decimal places.
12.000
5 Delete the two rows containing Poster art types.
7.000
6 Turn on the table total row. Delete the total in cell G104. In cells C104 and E104, select the function that
shows the largest edition size and the most expensive issue price. Type Largest Edition Size and
Highest Issue Price in cell A104.
9.000
7 Create a custom conditional format for the Issue Price range E2:E103, with these specifications:
•4 Traffic Lights icon set (Black, Red, Yellow, Green)
•Red icon when the number is greater than 1000 Number
•Yellow icon when the number is less than or equal to 1000 and greater than 500 Number
•Green icon when the number is less than or equal to 500 and greater than 250 Number
•Black icon when the number is less than or equal to 250.
10.000
8 Filter the table by the Red Traffic Light conditional formatting icon.
0.000
9 Answer the first question below the worksheet data based on the filtered table. Type your answer in cell
B107.
5.000
Updated: 04/18/2014
1
Current_Instruction.docx
Step
Points
Possible
Instructions
10 Answer the second question below the worksheet data based on the filtered table. Type your answer in
cell B108.
5.000
11 Answer the third question below the worksheet data based on the filtered table. Type your answer in cell
B109.
5.000
12 Answer the fourth question below the worksheet data based on the filtered table. Type your answer in
cell B110.
5.000
13 Answer the fifth question below the worksheet data based on the filtered table. Type your answer in cell
B111.
5.000
14 Set the column width for column A to 46.0. Select Landscape orientation, and set the scaling to fit the
data to 1 page.
8.000
15 Set 1-inch top and bottom margins. Set 0.3-inch left and right margins.
6.000
16 For column B, set the column width to 30. For columns C and D, set 8.5 column widths and apply Wrap
Text.
7.000
17 Insert a footer with the text Exploring Series on the left side, the sheet name code in the center, and the
file name code on the right side.
6.000
18 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.
0.000
Total Points
100.000
-
Rating:
/5
Solution: Exploring E CH4 H3 Artwork 2.0 Solution