EX16_XL_CH04_GRADER_ML2_HW - Artwork 1.2 Project Description: You work for a gal
ID: 3146151 • Letter: E
Question
EX16_XL_CH04_GRADER_ML2_HW - Artwork 1.2
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, hard-to-obtain 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
Instructions
Points Possible
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 range E2:E103 using the 4 Traffic Lights icon set (Black, Red, Yellow, Green). Edit the rule so that the red icon displays when the number value is greater than or equal to 1000; the yellow icon displays when the number value is less than 1000 and greater than or equal to 500; the green icon displays when the number value is less than 500 and greater than or equal to 250; and the black icon displays when the value is less than 250. Be sure to change the Type to Number before setting the rule for each icon.
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
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
this is the excel link
https://www.dropbox.com/s/homos7guvmm6tyx/these%20is%20the%20excel.xlsx?dl=0
Step
Instructions
Points Possible
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 range E2:E103 using the 4 Traffic Lights icon set (Black, Red, Yellow, Green). Edit the rule so that the red icon displays when the number value is greater than or equal to 1000; the yellow icon displays when the number value is less than 1000 and greater than or equal to 500; the green icon displays when the number value is less than 500 and greater than or equal to 250; and the black icon displays when the value is less than 250. Be sure to change the Type to Number before setting the rule for each icon.
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
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
Explanation / Answer
Art Type Edition Size Release Date Issue Price Sold Out Total original value A Lawyer More than Adequately Attired in Fine Print Anniversary Edition Canvas 250 Sep-12 $ 475 118,750 A Place of Her Own Limited Edition Canvas 250 Aug-08 $ 525 Yes 131,250 A Place of Her Own Masterwork Canvas Edition 50 Aug-08 $ 1,450 Yes 72,500 All That She Had Limited Edition Canvas 450 Dec-08 $ 395 177,750 Angel Unaware Limited Edition Canvas 250 Oct-09 $ 295 Yes 73,750 Angel Unobserved Smallwork Canvas Edition 350 Mar-10 $ 225 Yes 78750 Angel with Epaulet Limited Edition Canvas 550 Dec-06 $ 150 Yes 82,500 Angels of My Village Limited Edition Print 650 Jan-07 $ 165 Limited Availability 107,250 Arise and Shine Forth Masterwork Canvas Edition 25 Mar-12 $ 1,250 31,250 Benediction Anniversary Edition Canvas 300 Jul-10 $ 495 148,500 Benediction Masterwork Anniversary Edition 50 Jul-10 $ 995 Limited Availability 49,750 Cecelia Limited Edition Print 350 Sep-05 $ 195 Yes 68,250 Cecelia Masterwork Canvas Edition 250 Aug-05 $ 995 Yes 248,750 Courtship Limited Edition Canvas 375 Sep-08 $ 495 185625 Dusk Limited Edition Canvas 300 Jan-04 $ 495 Limited Availability 148500 Faith, Hope and Charity Limited Edition Canvas 275 Jul-02 $ 695 Yes 191,125 Faith, Hope and Charity Limited Edition Print 550 Jul-02 $ 185 Yes 101750 Faith, Hope and Charity Masterwork Canvas Edition 100 Jul-02 $ 1,850 Limited Availability 185,000 False Magic Limited Edition Canvas 375 Apr-08 $ 225 Yes 84,375 Finding Your Fish Smallwork Canvas Edition 375 Oct-09 $ 225 84,375 Fiona Limited Edition Canvas 400 Sep-07 $ 495 198,000 Fiona Limited Edition Print 450 Sep-07 $ 165 74,250 First Rose Smallwork Canvas Edition 250 Apr-09 $ 195 Yes 48,750 Garden Rendezvous Limited Edition Canvas Jun-02 $ 695 Yes '' Garden Rendezvous Limited Edition Print Jun-02 $ 185 ''' Guardian in the Woods Limited Edition Canvas 200 Jun-12 $ 395 79,000 Guardian in the Woods Limited Edition Print 250 Jun-12 $ 195 48,750 Hold to the Rod, the Iron Rod Limited Edition Canvas 550 Jun-07 $ 295 Yes 162,250 Hold to the Rod, the Iron Rod Limited Edition Print 1820 Jan-12 $ 175 Yes 318,500 If Pigs Could Fly Limited Edition Canvas 350 Jan-06 $ 325 Limited Availability 113,750 Isabella Limited Edition Canvas 375 Oct-03 $ 395 Yes 148,125 Isabella Limited Edition Print 750 Oct-03 $ 150 Yes 112,500 Jonah Anniversary Edition Canvas 450 Apr-10 $ 425 191,250 Lawrence Pretended Not to Notice That A Bear Had Become Anniversary Edition Canvas 501 May-08 $ 525 263025 Madonna with Two Angeles framed Limited Edition Canvas 375 Jun-05 $ 595 Limited Availability 223,125 Man Taking a Leek on a Tiled Wall for a Walk Smallwork Canvas Edition 450 Aug-12 $ 195 87,750 Men and Angels Limited Edition Canvas 350 Sep-06 $ 375 Yes 131,250 Men and Angels Limited Edition Print 550 Sep-06 $ 135 Yes 74,250 Monarch of All He Surveys Limited Edition Canvas 250 Jan-02 $ 325 Yes 81,250 Monarch of All He Surveys Limited Edition Print 750 Jan-02 $ 135 101,250 Music of Heaven Limited Edition Canvas 450 Oct-07 $ 225 Yes 101,250 Olde World Santa Anniversary Edition Canvas 375 Oct-02 $ 395 Yes 148,125 Once Upon a Time Masterwork Anniversary Edition 300 Mar-04 $ 1,750 Yes 525,000 One Light Anniversary Edition Canvas 250 May-12 $ 245 60,000 Outside the Box Limited Edition Canvas 325 Feb-09 $ 595 193,375 Parables Masterwork Anniversary Edition 75 Dec-09 $ 995 Limited Availability 74,625 Passage by Faith Limited Edition Canvas 250 Nov-12 $ 475 118,750 Passage by Faith Limited Edition Print 250 Nov-12 $ 165 41,250 Pilates Smallwork Canvas Edition 350 Oct-10 $ 275 96,250 Pilgrim Angel Smallwork Canvas Edition 250 Mar-12 $ 225 56,250 Poofy Guy on a Short Leash Limited Edition Canvas 300 Aug-04 $ 495 Limited Availability 148,500 Poofy Guy on a Short Leash Limited Edition Print 650 Aug-04 $ 160 Limited Availability 104,000 Portrait with Red Berries Limited Edition Canvas 350 Jun-03 $ 295 Yes 103,250 Portrait with Red Berries Limited Edition Print 550 Jun-03 $ 135 Yes 74,250 Princess in the Tower Limited Edition Canvas 250 Oct-12 $ 245 61,250 Resistance Training Limited Edition Canvas 550 Apr-07 $ 295 Yes 162,250 Saint with White Sleeves Limited Edition Canvas 300 Mar-05 $ 395 Yes 118,500 Saint with White Sleeves Limited Edition Print 550 Mar-05 $ 150 Limited Availability 82,500 Shakespearean Fantasy Limited Edition Canvas 250 May-11 $ 495 123,750 Shakespearean Fantasy Masterwork Canvas Edition 150 May-11 $ 950 142,500 Sleeper Lost in Dreams Limited Edition Canvas 150 Jan-03 $ 295 Yes 44,250 Sleeper Lost in Dreams Limited Edition Print 550 Jan-03 $ 135 Yes 74,250 Sometimes the Spirit Touches Us Through Our Weaknesses Anniversary Edition Canvas 550 Aug-09 $ 295 Yes 162250 St. Brendan The Navigator Limited Edition Canvas 275 Jan-04 $ 250 Yes 68,750 St. Brendan The Navigator Limited Edition Print 650 Jan-04 $ 140 Limited Availability 91,000 St. Nicholas of Myra Limited Edition Canvas 450 Aug-04 $ 260 Yes 117,000 Tempus Fugit Smallwork Canvas Edition 450 Apr-10 $ 195 Limited Availability 87,750 Ten Lepers Limited Edition Canvas 200 Oct-02 $ 650 Yes 130,000 Ten Lepers Limited Edition Print 550 Sep-02 $ 185 Yes 101,750 The Beggar Princess and the Magic Rose Limited Edition Canvas 650 Mar-07 $ 395 Yes 256,750 The Blind Leading the Blind Limited Edition Canvas 550 May-07 $ 550 302,500 The Blind Leading the Blind Limited Edition Print 450 May-07 $ 165 Limited Availability 74,250 The Bride Limited Edition Canvas 175 May-05 $ 475 Yes 83,125 The Bride Limited Edition Print 450 May-05 $ 145 Yes 65,250 The Burden of the Responsible Man Anniversary Edition Canvas 600 Nov-07 $ 425 Yes 255,000 The Chess Match Limited Edition Canvas 300 Feb-11 $ 695 208,500 The Chess Match Limited Edition Print 500 Feb-11 $ 225 112,500 The Enoch Altarpiece framed Limited Edition Canvas 250 Jun-04 $ 1,595 Yes 398,750 The Golden Ball Limited Edition Canvas 275 Sep-10 $ 325 89,375 The Listener Limited Edition Canvas 175 Mar-06 $ 650 Yes 113,750 The Listener Limited Edition Print 350 Mar-06 $ 195 Limited Availability 68,250 The Messenger Limited Edition Print 200 Jun-04 $ 775 155,000 The Nest Limited Edition Canvas 200 Sep-11 $ 495 99,000 The Oldest Angel Anniversary Edition Canvas 350 Nov-10 $ 395 138,250 The Pink Ribbon Limited Edition Canvas 250 Oct-05 $ 250 Limited Availability 62,500 The Responsible Woman Anniversary Edition Canvas 450 Aug-06 $ 650 Yes 292,500 The Return of the Fablemaker Limited Edition Canvas 125 Aug-09 $ 495 61,875 The Return of the Fablemaker Masterwork Canvas Edition 25 Aug-09 $ 995 Yes 24,875 The Rich Young Ruler Limited Edition Canvas 550 Mar-08 $ 545 Limited Availability 299,750 The Royal Processional Masterwork Anniversary Edition 215 Jan-05 $ 1,250 Yes 268,750 The Tie That Binds Limited Edition Canvas 250 Feb-10 $ 750 Limited Availability 187,500 The Tie That Binds Limited Edition Print 550 Feb-10 $ 250 137,500 The Yellow Rose Limited Edition Canvas 250 May-09 $ 495 123,750 Three Wise Men in a Boat Limited Edition Canvas 250 Nov-11 $ 295 73,750 Touching the Hem of God Limited Edition Canvas 475 Mar-03 $ 650 Yes 308,750 Touching the Hem of God Limited Edition Print 750 Mar-03 $ 185 Yes 138,750 Twilight Limited Edition Canvas 300 Oct-04 $ 495 Yes 148,500 Twilight Limited Edition Print 550 Oct-04 $ 160 Limited Availability 88,000 Two Sisters Anniversary Edition Canvas 200 Mar-12 $ 695 139,000 Virtue Limited Edition Canvas 550 Jan-08 $ 525 288,750 We Three Kings Limited Edition Canvas 450 Sep-03 $ 695 312,750 We Three Kings Limited Edition Print 950 Sep-03 $ 160 Limited Availability 152,000 Largest Edition Size and Highest Issue Price Analysis for Most Expensive Art 1. What was the largest issue size? 1820 2. How many pieces are some sort of "Masterwork"? 10 3. How many pieces are not sold out? 38 4. Which piece had the highest issue price? Faith, Hope and Charity 5. What type of art was the highest issue price piece? Masterwork Canvas Edition
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.