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. Christensens 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

Art	Type	Edition Size	Release Date	Issue Price	Sold Out
A Lawyer More than Adequately Attired in Fine Print	Anniversary Edition Canvas	250	Sep-12	$ 475
A Place of Her Own	Limited Edition Canvas	250	Aug-08	$ 525	Yes
A Place of Her Own	Masterwork Canvas Edition	50	Aug-08	$ 1,450	Yes
All That She Had	Limited Edition Canvas	450	Dec-08	$ 395
Angel Unaware	Limited Edition Canvas	250	Oct-09	$ 295	Yes
Angel Unobserved	Smallwork Canvas Edition	350	Mar-10	$ 225	Yes
Angel with Epaulet	Limited Edition Canvas	550	Dec-06	$ 150	Yes
Angels of My Village	Limited Edition Print	650	Jan-07	$ 165	Limited Availability
Arise and Shine Forth	Masterwork Canvas Edition	25	Mar-12	$ 1,250
Arise and Shine Forth	Poster		Apr-12	$ 20
Benediction	Anniversary Edition Canvas	300	Jul-10	$ 495
Benediction	Masterwork Anniversary Edition	50	Jul-10	$ 995	Limited Availability
Cecelia	Limited Edition Print	350	Sep-05	$ 195	Yes
Cecelia	Masterwork Canvas Edition	250	Aug-05	$ 995	Yes
Conversation Around Fish	Poster		Sep-02	$ 20
Courtship	Limited Edition Canvas	375	Sep-08	$ 495
Dusk	Limited Edition Canvas	300	Jan-04	$ 495	Limited Availability
Faith, Hope and Charity	Limited Edition Canvas	275	Jul-02	$ 695	Yes
Faith, Hope and Charity	Limited Edition Print	550	Jul-02	$ 185	Yes
Faith, Hope and Charity	Masterwork Canvas Edition	100	Jul-02	$ 1,850	Limited Availability
False Magic	Limited Edition Canvas	375	Apr-08	$ 225	Yes
Finding Your Fish	Smallwork Canvas Edition	375	Oct-09	$ 225
Fiona	Limited Edition Canvas	400	Sep-07	$ 495
Fiona	Limited Edition Print	450	Sep-07	$ 165
First Rose	Smallwork Canvas Edition	250	Apr-09	$ 195	Yes
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
Guardian in the Woods	Limited Edition Print	250	Jun-12	$ 195
Hold to the Rod, the Iron Rod	Limited Edition Canvas	550	Jun-07	$ 295	Yes
Hold to the Rod, the Iron Rod	Limited Edition Print	1820	Jan-12	$ 175	Yes
If Pigs Could Fly	Limited Edition Canvas	350	Jan-06	$ 325	Limited Availability
Isabella	Limited Edition Canvas	375	Oct-03	$ 395	Yes
Isabella	Limited Edition Print	750	Oct-03	$ 150	Yes
Jonah	Anniversary Edition Canvas	450	Apr-10	$ 425
Lawrence Pretended Not to Notice That A Bear Had Become	Anniversary Edition Canvas	501	May-08	$ 525
Madonna with Two Angeles framed	Limited Edition Canvas	375	Jun-05	$ 595	Limited Availability
Man Taking a Leek on a Tiled Wall for a Walk	Smallwork Canvas Edition	450	Aug-12	$ 195
Men and Angels	Limited Edition Canvas	350	Sep-06	$ 375	Yes
Men and Angels	Limited Edition Print	550	Sep-06	$ 135	Yes
Monarch of All He Surveys	Limited Edition Canvas	250	Jan-02	$ 325	Yes
Monarch of All He Surveys	Limited Edition Print	750	Jan-02	$ 135
Music of Heaven	Limited Edition Canvas	450	Oct-07	$ 225	Yes
Olde World Santa	Anniversary Edition Canvas	375	Oct-02	$ 395	Yes
Once Upon a Time	Masterwork Anniversary Edition	300	Mar-04	$ 1,750	Yes
One Light	Anniversary Edition Canvas	250	May-12	$ 245
Outside the Box	Limited Edition Canvas	325	Feb-09	$ 595
Parables	Masterwork Anniversary Edition	75	Dec-09	$ 995	Limited Availability
Passage by Faith	Limited Edition Canvas	250	Nov-12	$ 475
Passage by Faith	Limited Edition Print	250	Nov-12	$ 165
Pilates	Smallwork Canvas Edition	350	Oct-10	$ 275
Pilgrim Angel	Smallwork Canvas Edition	250	Mar-12	$ 225
Poofy Guy on a Short Leash	Limited Edition Canvas	300	Aug-04	$ 495	Limited Availability
Poofy Guy on a Short Leash	Limited Edition Print	650	Aug-04	$ 160	Limited Availability
Portrait with Red Berries	Limited Edition Canvas	350	Jun-03	$ 295	Yes
Portrait with Red Berries	Limited Edition Print	550	Jun-03	$ 135	Yes
Princess in the Tower	Limited Edition Canvas	250	Oct-12	$ 245
Resistance Training	Limited Edition Canvas	550	Apr-07	$ 295	Yes
Saint with White Sleeves	Limited Edition Canvas	300	Mar-05	$ 395	Yes
Saint with White Sleeves	Limited Edition Print	550	Mar-05	$ 150	Limited Availability
Shakespearean Fantasy	Limited Edition Canvas	250	May-11	$ 495
Shakespearean Fantasy	Masterwork Canvas Edition	150	May-11	$ 950
Sleeper Lost in Dreams	Limited Edition Canvas	150	Jan-03	$ 295	Yes
Sleeper Lost in Dreams	Limited Edition Print	550	Jan-03	$ 135	Yes
Sometimes the Spirit Touches Us Through Our Weaknesses	Anniversary Edition Canvas	550	Aug-09	$ 295	Yes
St. Brendan The Navigator	Limited Edition Canvas	275	Jan-04	$ 250	Yes
St. Brendan The Navigator	Limited Edition Print	650	Jan-04	$ 140	Limited Availability
St. Nicholas of Myra	Limited Edition Canvas	450	Aug-04	$ 260	Yes
Tempus Fugit	Smallwork Canvas Edition	450	Apr-10	$ 195	Limited Availability
Ten Lepers	Limited Edition Canvas	200	Oct-02	$ 650	Yes
Ten Lepers	Limited Edition Print	550	Sep-02	$ 185	Yes
The Beggar Princess and the Magic Rose	Limited Edition Canvas	650	Mar-07	$ 395	Yes
The Blind Leading the Blind	Limited Edition Canvas	550	May-07	$ 550
The Blind Leading the Blind	Limited Edition Print	450	May-07	$ 165	Limited Availability
The Bride	Limited Edition Canvas	175	May-05	$ 475	Yes
The Bride	Limited Edition Print	450	May-05	$ 145	Yes
The Burden of the Responsible Man	Anniversary Edition Canvas	600	Nov-07	$ 425	Yes
The Chess Match	Limited Edition Canvas	300	Feb-11	$ 695
The Chess Match	Limited Edition Print	500	Feb-11	$ 225
The Enoch Altarpiece framed	Limited Edition Canvas	250	Jun-04	$ 1,595	Yes
The Golden Ball	Limited Edition Canvas	275	Sep-10	$ 325
The Listener	Limited Edition Canvas	175	Mar-06	$ 650	Yes
The Listener	Limited Edition Print	350	Mar-06	$ 195	Limited Availability
The Messenger	Limited Edition Print	200	Jun-04	$ 775
The Nest	Limited Edition Canvas	200	Sep-11	$ 495
The Oldest Angel	Anniversary Edition Canvas	350	Nov-10	$ 395
The Pink Ribbon	Limited Edition Canvas	250	Oct-05	$ 250	Limited Availability
The Responsible Woman	Anniversary Edition Canvas	450	Aug-06	$ 650	Yes
The Return of the Fablemaker	Limited Edition Canvas	125	Aug-09	$ 495
The Return of the Fablemaker	Masterwork Canvas Edition	25	Aug-09	$ 995	Yes
The Rich Young Ruler	Limited Edition Canvas	550	Mar-08	$ 545	Limited Availability
The Royal Processional	Masterwork Anniversary Edition	215	Jan-05	$ 1,250	Yes
The Tie That Binds	Limited Edition Canvas	250	Feb-10	$ 750	Limited Availability
The Tie That Binds	Limited Edition Print	550	Feb-10	$ 250
The Yellow Rose	Limited Edition Canvas	250	May-09	$ 495
Three Wise Men in a Boat	Limited Edition Canvas	250	Nov-11	$ 295
Touching the Hem of God	Limited Edition Canvas	475	Mar-03	$ 650	Yes
Touching the Hem of God	Limited Edition Print	750	Mar-03	$ 185	Yes
Twilight	Limited Edition Canvas	300	Oct-04	$ 495	Yes
Twilight	Limited Edition Print	550	Oct-04	$ 160	Limited Availability
Two Sisters	Anniversary Edition Canvas	200	Mar-12	$ 695
Virtue	Limited Edition Canvas	550	Jan-08	$ 525
We Three Kings	Limited Edition Canvas	450	Sep-03	$ 695
We Three Kings	Limited Edition Print	950	Sep-03	$ 160	Limited Availability
Analysis for Most Expensive Art
1. What was the largest issue size?
2. How many pieces are some sort of "Masterwork"?
3. How many pieces are not sold out?
4. Which piece had the highest issue price?
5. What type of art was the highest issue price piece?

100.000