Steps to Perform: Step Instructions Points Possible 1 0 E 5 2 3 Start Excel. Download and open the file named e04_grader_h3.xlsx Convert the data to a table and apply Table Style Medium 5. Sort the table by the Type in alphabetical order, and then by Release Date from newest to oldest. 5 JUUS 4 12 Type Total Original Value in cell G1. In cell G2, enter a structured formula to compute the original value. If Edition Size is greater than 0, then multiply Edition Size by Issue Price; otherwise display an empty coll using". Format the range G2:G105 with Accounting Number Format with zero decimal places. Delete the two rows containing Poster art types. 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. 7 5 9 6 7 10 Issue Price $ 42 $ 16 $ 24 47 S 19 $ 39 $ 19 $ 24 $ 2 $ 1,25 $ 22 $ 69 $ $ 29 $ 49 $ 49 $ 95 $ 69 $ 22 $ 39 $ 27 $ 32 $ 49 $ 99 $ 42 DS 19 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. Filter the table by the Red Traffic Light conditional formatting icon. Answer the first question below the worksheet data based on the filtered table. Type your answer in cell B107. O 8 5 9 5 10 Answer the second question below the worksheet data based on the filtered table. Type your answer in cell B108. Answer the third question below the worksheet data based on the filtered table. Type your answer in cell B109. 5 11 . 5 12 Answer the fourth question below the worksheet data based on the filtered table. Type your answer in cell B110. 5 13 Answer the fifth question below the worksheet data based on the filtered table. Type your answer in cell B111 8 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. 6 15 Set 1-inch top and bottom margins. Set 0.3-inch left and right margins. 7 16 For column B, set the column width to 30. For columns C and D. set 8.5 column widths and apply Wrap Text. 6 17 Insert a footer with the text Exploring Series on the left side, the sheet name code in 01 G Type 1 Column 2 Passage by Fath 3 Passage by Faith 4 Princess in the Tower 5 A lawyer More than Adequately Astired in Fire Print 6 Man Takingaleek on a Tled Walfora Walk 7 Guardian in the Woods B Guardian in the Woods 9 One Licht 10 Ace and inforth 11 Arise and Shine Forth 12 Page 13 Two Sisters 14 Hold to the Rod, the Iron Rod 15 Three Wise Menina Boat 16 The Nest 17 Shakespearean Fan 18 Shakespearean Fantasy 19 The Chess Match 20 The Chess Match 21 The Oldest Angel 22 Plates 23 The Golden Ball 24 Benediction 25 Benediction 26 orah 27 Tempus Fut 20 Angeloved 29 The Te That inds 30 The Te That Birds 31 Parables 32 Angel Unire 33 Finding Your Fish 34 Sometimes the Spirn Touches us through a Weaknesses 35 The Return of the Fathemaker 36 The Return of the fobiemaker 37 The Yellow Rose Umited Erion Canvas Limited Edition Print Limited Edition Canvas Anniversary Edition Canvas Smallword Canvas Edition Limited Edition Canvas Umited Edition Print Anniversary Edition Canvas Poster Masterwork Canvas Edition Smallwork Carwas Edition Anniversary Edition Canvas Limited Edition Print Limited Edition Canvas Umited Edition Canvas Limited Edition Canvas Masterwork Canvastoon Limited Edition Carvas Limited Edition Print Anniversary Edition Canvas Smart Canvas Edition Limited Edition Cars Anniversary Coton Canvas Masterwork Anniversary Edition Anniversary Edition Canvas Seawor Canvasortion Smalw Canvas din Limited Edition Canvas Lmhed to Print Masterwe Anniversary Edition Limited Edition Canvas Smalwork Canvas Eston Anniversary Edition Canvas Limted on Materwork Carson Limited Edson Canvas Smallwork Canvasdon Limited Edtion Canvas Limited Edition Canvas Uiteiten Cars Limited Edition Canvas Mastewation Anniversary Edition Canvas Led Eton Canvas United Edition Canvas Limited Edition Anniversary Edition Canva Ledion med Edson Canvas F Edition Sle Release Date Price Sold Out 250 Nov 12 5 475 250 Now 12" 165 250 Oc-12 245 250 Sep 12 5 475 450 Aug 12 s 195 200 Jur-12 $ 395 250 Jus 125 195 250 May 12 $ 245 Apr 12 $ 20 Man 125 1.250 250 Mar 12 225 200 Mar 12 $ 695 1820 Jan 12 $ 175 Yes 250 Nov 11 $ 295 200 Sep-11 $ 495 250 May 11 495 150 May 11 5 950 300 Feb-11 5 695 500 Feb 11 $ 225 350 Nov.90 395 350 Oct 105 275 275 Sep-10 S 325 300 -10$ 495 50 -30 995 Limited Avality 450 Apr-105 425 490 Apr 10 $ 195 Limited Availability 350 Mar 105 225 Yes 250 Feb-105 750 Limited Avalabit feb-10 S 250 25 Deco 935 med Avality 250 Oct-00 $ 295 Yes 375 OS 225 550 AS 295 Yes 125 Aug-03 135 25 Aug-09 995 Yes 250 May 09 5 495 250 Apro 195 Yes 325 Feb-09 $ 595 450 Dec- os 395 375 SepS 435 250 Aug-08 $25 Yes 50 AOS 1450 Yes 501 May. 535 375 Aero 5 225 Yes 550 Maros 545 Limited Away 550 Tan- os 25 100 Now O S 25 Yes 450 Oct-07 235 Yes 400 Sep 07 015 99 Outside Box 40 Al The Hed 61 Court 42 A Place of Her Own 4) A Place of Her Own 4 Lawrence Pretended Not to Notice That Abar Had become 45 F Mage 46 The Rich Young 4 The Burden of the sense Music of even Christensen + Limited Edition Canvas Limited Edition Print 25 75 s Monarch of All He Surveys 06 07 Analysis for Most Expensive Art 108 1. What was the largest issue size? 09 2. How many pieces are some sort of "Masterwork"? 10 3. How many pieces are not sold out? 11 4. Which piece had the highest issue price? 112 5. What type of art was the highest issue price plece? 113 114 115 Steps to Perform: Step Instructions Points Possible 1 0 E 5 2 3 Start Excel. Download and open the file named e04_grader_h3.xlsx Convert the data to a table and apply Table Style Medium 5. Sort the table by the Type in alphabetical order, and then by Release Date from newest to oldest. 5 JUUS 4 12 Type Total Original Value in cell G1. In cell G2, enter a structured formula to compute the original value. If Edition Size is greater than 0, then multiply Edition Size by Issue Price; otherwise display an empty coll using". Format the range G2:G105 with Accounting Number Format with zero decimal places. Delete the two rows containing Poster art types. 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. 7 5 9 6 7 10 Issue Price $ 42 $ 16 $ 24 47 S 19 $ 39 $ 19 $ 24 $ 2 $ 1,25 $ 22 $ 69 $ $ 29 $ 49 $ 49 $ 95 $ 69 $ 22 $ 39 $ 27 $ 32 $ 49 $ 99 $ 42 DS 19 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. Filter the table by the Red Traffic Light conditional formatting icon. Answer the first question below the worksheet data based on the filtered table. Type your answer in cell B107. O 8 5 9 5 10 Answer the second question below the worksheet data based on the filtered table. Type your answer in cell B108. Answer the third question below the worksheet data based on the filtered table. Type your answer in cell B109. 5 11 . 5 12 Answer the fourth question below the worksheet data based on the filtered table. Type your answer in cell B110. 5 13 Answer the fifth question below the worksheet data based on the filtered table. Type your answer in cell B111 8 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. 6 15 Set 1-inch top and bottom margins. Set 0.3-inch left and right margins. 7 16 For column B, set the column width to 30. For columns C and D. set 8.5 column widths and apply Wrap Text. 6 17 Insert a footer with the text Exploring Series on the left side, the sheet name code in 01 G Type 1 Column 2 Passage by Fath 3 Passage by Faith 4 Princess in the Tower 5 A lawyer More than Adequately Astired in Fire Print 6 Man Takingaleek on a Tled Walfora Walk 7 Guardian in the Woods B Guardian in the Woods 9 One Licht 10 Ace and inforth 11 Arise and Shine Forth 12 Page 13 Two Sisters 14 Hold to the Rod, the Iron Rod 15 Three Wise Menina Boat 16 The Nest 17 Shakespearean Fan 18 Shakespearean Fantasy 19 The Chess Match 20 The Chess Match 21 The Oldest Angel 22 Plates 23 The Golden Ball 24 Benediction 25 Benediction 26 orah 27 Tempus Fut 20 Angeloved 29 The Te That inds 30 The Te That Birds 31 Parables 32 Angel Unire 33 Finding Your Fish 34 Sometimes the Spirn Touches us through a Weaknesses 35 The Return of the Fathemaker 36 The Return of the fobiemaker 37 The Yellow Rose Umited Erion Canvas Limited Edition Print Limited Edition Canvas Anniversary Edition Canvas Smallword Canvas Edition Limited Edition Canvas Umited Edition Print Anniversary Edition Canvas Poster Masterwork Canvas Edition Smallwork Carwas Edition Anniversary Edition Canvas Limited Edition Print Limited Edition Canvas Umited Edition Canvas Limited Edition Canvas Masterwork Canvastoon Limited Edition Carvas Limited Edition Print Anniversary Edition Canvas Smart Canvas Edition Limited Edition Cars Anniversary Coton Canvas Masterwork Anniversary Edition Anniversary Edition Canvas Seawor Canvasortion Smalw Canvas din Limited Edition Canvas Lmhed to Print Masterwe Anniversary Edition Limited Edition Canvas Smalwork Canvas Eston Anniversary Edition Canvas Limted on Materwork Carson Limited Edson Canvas Smallwork Canvasdon Limited Edtion Canvas Limited Edition Canvas Uiteiten Cars Limited Edition Canvas Mastewation Anniversary Edition Canvas Led Eton Canvas United Edition Canvas Limited Edition Anniversary Edition Canva Ledion med Edson Canvas F Edition Sle Release Date Price Sold Out 250 Nov 12 5 475 250 Now 12" 165 250 Oc-12 245 250 Sep 12 5 475 450 Aug 12 s 195 200 Jur-12 $ 395 250 Jus 125 195 250 May 12 $ 245 Apr 12 $ 20 Man 125 1.250 250 Mar 12 225 200 Mar 12 $ 695 1820 Jan 12 $ 175 Yes 250 Nov 11 $ 295 200 Sep-11 $ 495 250 May 11 495 150 May 11 5 950 300 Feb-11 5 695 500 Feb 11 $ 225 350 Nov.90 395 350 Oct 105 275 275 Sep-10 S 325 300 -10$ 495 50 -30 995 Limited Avality 450 Apr-105 425 490 Apr 10 $ 195 Limited Availability 350 Mar 105 225 Yes 250 Feb-105 750 Limited Avalabit feb-10 S 250 25 Deco 935 med Avality 250 Oct-00 $ 295 Yes 375 OS 225 550 AS 295 Yes 125 Aug-03 135 25 Aug-09 995 Yes 250 May 09 5 495 250 Apro 195 Yes 325 Feb-09 $ 595 450 Dec- os 395 375 SepS 435 250 Aug-08 $25 Yes 50 AOS 1450 Yes 501 May. 535 375 Aero 5 225 Yes 550 Maros 545 Limited Away 550 Tan- os 25 100 Now O S 25 Yes 450 Oct-07 235 Yes 400 Sep 07 015 99 Outside Box 40 Al The Hed 61 Court 42 A Place of Her Own 4) A Place of Her Own 4 Lawrence Pretended Not to Notice That Abar Had become 45 F Mage 46 The Rich Young 4 The Burden of the sense Music of even Christensen + Limited Edition Canvas Limited Edition Print 25 75 s Monarch of All He Surveys 06 07 Analysis for Most Expensive Art 108 1. What was the largest issue size? 09 2. How many pieces are some sort of "Masterwork"? 10 3. How many pieces are not sold out? 11 4. Which piece had the highest issue price? 112 5. What type of art was the highest issue price plece? 113 114 115