Quote:
Originally Posted by Buzz-cp
I use Excel for grading. I'd like to prepare progress reports that I could print out.
I have grade categories and assignments in columns and names/IDs in rows along with points for each. Something like this:
Code:
Exam Lab
Name 1 2 3 4 1 2 3 4
Possible -> 100 100 100 100 25 25 25 25
Bob Barker 95 89 75 79 24 15 23 0
Jens Kruger 88 59 65 47 19 17 20 21
So we make a report for Bob Barker on a new worksheet, and it looks like this:
Code:
Name: Bob Barker
Date: 1/1/2010
Possible Score
Exam 1 100 95
Exam 2 100 89
Exam 3 100 75
Exam 4 100 79
Lab 1 25 24
Lab 2 25 15
..etc
Any way to do this without much VBA? I can muck VBA and figure out how to edit existing scripts, but haven't really learned it.
Pivot tables make this incredibly easy. (instructions for excel 2007)
Format your data like above, but instead of having exam a row above the number name them Exam 1, Exam 2, Lab 1 etc.
In the first column name the columm Name - include possible as a name here.
Select the whole table, and go Insert menu then Pivot table, just click OK to put it on a new worksheet.
On a new tab you will see a Pivot table appear with the field list on the right.
Drag the Name field to the Column Labels box
From the drop down on the pivto table now, select Bob Barker (or whomever) AND Possible - right click the Grand Total box and select Remove Grand Total.
If Possible isnt on the right most column, right click that and select Move -> Move possible to the end
Pull in the Exam and labs to the Values box (or you can just click off the check box)
In the Column labes box, pull the Values selection to the Row Labels box.
You're done.
You can rename the Sum of Exam 1 or whatever to Exam 1 if you wish.
One advantage here is you can just click on Column Labels dropdown to select any name you want to change this report.
If you add in more Labs or Exams (or people) (they don't need to be in order on the data sheet you can just reorder them on the pivot), make sure you increase the pivot source area to include them. To do this - click on the pivot and then click options under the pivot table tools and the select change data source and reselect the data for the table. The pivot will refresh.
This is a VERY basic use of pivot tables, and the beneft becomes more and more obvious with more detailed sets of data where you can drill down, summerize by various ways, show values as % etc...