CogSci 3
Spring 04
Assignment 6 -- Excel -- Grading Criteria

1. 39 points maximum
   1. The assignment was graded by opening each student document. The order of the features described here is not necessarily the order in which they would have been created. in Excel and looking for the following features.
2. 1 point -- a correctly named cg3xzz-6 file in their hw6 folder; if you can locate the file easily, no deduction, otherwise no points. No points off for a double file name extension (.xls.xls).
3. 2 points -- correct parsing/copying of quiz data
4. 1 point -- converting quiz data to numbers
5. 2 points -- correct parsing/copying of names
6. 2 points -- correct sorting of names with quiz data
   1. Krusty is student A0001 with total 53
   2. Montgomery is student A0021 with total 58
   3. Langdon Alger is student A0041 with total 74
7. 1 point -- ID, Quiz, Total headings on columns
8. 2 points -- correct sums column (check for formulas in column)
9. 3 points -- correct 3 rows of averages (check for formulas in row)
   Note: the values should be same for the Average() and Count() formulas, but different for the Rows() version. However, if the student interpretted the instructions to actually fill in the blank cells, the averages will all be the same. No deduction. But check Selma and Patti's scores to see that there are no blanks, but 0 instead.
   1. 1 point -- Sum()/Rows()
   2. 1 point -- Sum()/Count()
   3. 1 point -- Average()
10. 2 points -- correct stdev (check for formulas in row)
11. 2 points -- correct format (0.00) on all 4 rows (averages, and stdev)
12. 1 point -- define a Name MaxScore, check Insert->Names...
13. 1 point -- P/NP formulas should use MaxScore instead of 80
14. 1 point -- Letter grades formulas should use MaxScore instead of 80, in all 3 "if"s.
15. 2 points -- correct P/NP grade assignments (check for formulas in column)
16. 3 points -- correct letter grade assignments (check for formulas in column)
17. 1 points -- deleting extra ID column
18. 4 points -- creating a proper looking chart
    1. -1, not sorted in correct order
    2. -1, not in color
    3. -1, no title
    4. -1, no grid
19. 4 points -- creating pivot table and pie chart
    1. 2 points, pivot table
    2. 2 point, pie chart with % for labels
20. 1 point -- name and account in sheet name tab instead of Sheet1, "Totals Chart" for Sheet2, "Pivot Table" for Sheet3
21. Sort by Last Name -- 3 points
    1. 1 points for creating a new sheet called "Sort by Name" which has a copy of the student names, and scores, etc.
    2. 2 points for a correct formula, probably something like:
       RIGHT(A2,LEN(A2)-SEARCH(" ",A2)) & ", " & LEFT(A2,SEARCH(" ",A2))
       and Filling down through the column and labeling the Column "Names"

©opyright 2001-2004 Mark R. Wallen Last updated: Fri May 21 09:08:32 2004