Excel 2

Office 2010 – myitlab:grader – Instructions Exploring! Excel Ch. 04 – IRCD Project

Student Grade Sheet

Project Description:
In this project, you will format a range of data as a table, apply sorting and filtering techniques, and use a structured reference formula. You will also prepare the worksheet for printing and freeze panes to improve data viewing.

Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step Instructions Points Possible
1 Start Excel. Open the downloaded Excel workbook named Exploring_e04_Grader_IR.xlsx. 0
2 Click cell C3 and then freeze the panes of the worksheet so that when you scroll to the right, the students’ first and last names remain visible. 10
3 Move the Page 1 page break so that it is between columns K and L of the worksheet. View the print preview of the worksheet and then return to Normal view. 8
4 Format the data in the range A3:S14 as a table using Table Style Medium 20. 10
5 Sort the Earned Points column from the largest to the smallest value. 0
6 Sort the Test Average column from the smallest to the largest value. 10
7 In cell S4, enter a structured reference formula that calculates the Final Grade with Curve. Multiply the Earned Points by 1.02 (the formula will copy down to the rest of the column automatically). 10
8 Filter the data in the Final Grade with Curve column so that only the records with final grades of at least (greater than or equal to) 60 are displayed. 10
9 Insert a Total Row at the bottom of the table. Ensure that the function at the bottom of the Test Average column is set to Average and the Final Grade with Curve function is set to Count. 10
10 Select the range L5:L14. Set a formatting rule that will highlight cells that have a value greater than 79.99 with Green Fill with Dark Green Text. 10
11 Apply the Light Blue Data Bars with Gradient Fill to the range S5:S14. 10
12 Filter the Test Average column so that only the cells with the light green fill color are displayed (your worksheet should now display only those students whose test averages are 80.00 or higher). 9
13 Prepare the worksheet for printing by changing the orientation to Landscape. 3
14 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0
Total Points 100

Updated on: 22/2/2010 1 E_CH04_EXPV1_IRCD _Instructions x

Student Grade Sheet

Student Grade Sheet

8

0

100

100 100 100 100

88

.00

100 100 100 20

.98

95 100 0 0 0

80 0

8

Johnson

100 98 100 100 100 100

92

40 100 100 100 20

50 100 90 100 100 88

90

.00

98 100 99 20

100 92 0 0 100

30 80

97 100 100 100 98 29

74

.50

40 93 100

.5

98 94 100

100 96 29 78

96 74 85

98 100 100 100 100 100 30

84

94 100 97 19

96 0 0 0 0 19 6

0

13 0 0 0 0

93 0 100 0 100

18 79 69

92 80 86 17

Last Name	First Name	Word 2	Excel 2	Access 2	Access 3	PowerPoint 2	Homework Avg.	Homework Points	Test 2	Test 3	Test Average	Test Points	Project 1	Project 2	Project Average	Project Points	Earned Points	Final Grade with Curve
Barndt	Alexander				8											1																	0	88																												100		95			94	28		8	6	77	81.	50	41						98		99				20	88.81
Hopp	Taylor	83			97			29				92			90	45			93
	Johnson	Lavonda	39	12	70				80	75.00	38			40	57.20
Tiffany			30		69	80.50	90.	13
Kim	Samuel	26		78		84	42	88.20
Kirby	Casey	58		18			74	47	60.50	65	72.5	15	62.27
MacDonald	Zoe	91		85		79				96			19	88.33
Neithammer	Cathie	86	67	72.50	36		17	81.93
Prater	Keane	89	86.50	43	92.53
Reid	Brian	52	26.00	18.76
Simmons	Sandra	59	74.00	37	71.78