OVERVIEW:

• This gradebook will allow you to record as many different items as you wish to use for assessment, and will automatically calculate grades.
• You may indicate when an item has been used for assessment, so that it is included into grade calculations, for current standings.
• Each item may be weighted to vary the effect on the cumulative grade.
  • For example the Midterm is 30% of the cumulative grade in the example template, but is evaluated on a scale of 100 points possible
• Each item may be scored on whatever basis is most appropriate for the instrument,
  • All items might be based on 10 points, or 100, based on the granularity you wish to capture in the assessment
  • So a quiz might be based on 10 points, while an exam is based on 100, or they might both be 100, depending on the granularity you wish to capture.
  • The weight of the item should be adjusted to make the impact relative, the quiz is weighted as 5%, while the exam is weighted as 25%, for example.
• The breakpoint on each letter grade can be set, and may be changed by the instructor, to massage final grade distributions.
• There are attendance sheets for classes that meet either 2 or 3 times a week, which can be used to generate an attendance score
  • Records are kept on either sub-worksheet, then calculations are made for each student and a value is called in to the Scores page automatically
• There are also two different multi-part worksheets for assessments that have multiple phases, and the results are either summed or averaged
  • Sub-worksheet Multipart Ave generates the average of all scores entered, and calls that into the Scores page
  • These worksheets have all of the formulas built in, and automatically call the student's names in from the Scores sheet, and

INSTRUCTIONS:

• Don’t try to change colored text!
• Blue values are pulled from another area of the workbook
• Red values are generated by calculations
  • if you want to see what either colored text cell is doing, click on it and read the code in the formula bar at the top of the window
• A green outline indicates values you must fill in so other calculations work

SETUP:

Download a copy of gradebook3.xls

• Click to download: gradebook-v3.xls
• Determine where to save the file on your hard drive
• Give it a new name based on the class for which it will be used
• Open the new gradebook file

Full address for gradebook.xls:
http://abacus.bates.edu/ils/learn/gradebook/gradebook-v3.xls

Add students: Scores tab

• Click on the Scores tab
  (lower left corner)
• Adjust rows based on number of students:
  default is 15

• To add students:
• click on the row number to select row 16
• drag the square handle in the lower left corner of the selection
• down to enclose all of the extra rows for additional student names

• To remove students:
• click to select excess rows, Edit: Clear: All

• Replace the student names with ones from your class roster

• follow the instructions for data from Banner
• or type them in manually from a current roster

Add columns to record grades: Scores tab

• figure out how many items you will use to evaluate the students

• Add columns:

• click on the letter of column F to select it
• Edit: Copy, then Edit: Paste (repeat to add as many as you require)
• change the names of each column to suit your needs

• Clear old scores:

• click & drag to select all of the cells where scores will be recorded
• (do not include Cumulative Score, or anything to the right of it)
• (do not include any column with blue text!)
• Edit: Clear: Contents

When student grades are recorded, AND the assessment is put in use, AND the weight is set properly AND the points possible are recorded properly, then the current grade and class rank are calculated automatically.

Set the break points for grades: breaks tab

• Click on the breaks tab (lower left corner)
• in the Score column
• alter the numerical values to reflect the breaking points for each grade

• when student grades are recorded, the graph at the bottom gives an overview of the grade distribution for the entire class

USE:

Record student grades: Scores tab

• Click on the Scores tab (lower left corner)
• type in the numerical value each student received on the assessment

CHANGE ONLY THE BLACK VALUES!

• grades from attendance are entered and calculated on a separate worksheet and pulled into the Scores worksheet
• grades from multipart worksheets are recorded there, then either averaged or summed, and then pulled into the Scores worksheet

DO NOT CHANGE ANY RED OR BLUE VALUES!

• blue values have been called from another worksheet
• red values are generated by equations
• if you manually change either it will break the gradebook

When student grades are recorded, basic stats are calculated automatically for each assessment, and are located at the bottom of the worksheet, under the column for which they are relevant.

Calculate student grades: Scores tab

• in the Use? row

• enter a 1 for an assessment that should be used for calculations
• enter a 0 for an assessment that is not yet being used

• in the Weight row

• enter a numeric value that represents how much of the total grade each assessment is worth (think of them as percentages of 100)

• in the Points row

• enter the total number of possible points for the instrument
• this can be any scale, out of 5 points, 10 points 25 points, or a 100, the calculation works for each assessment individually, so long as you enter the number of points possible here

• Student grades should update as you fill in information
• Statistics for each assessment should generate automatically at the bottom of each column

Review the grade distribution: breaks tab

• Click on the breaks tab (lower left corner)
• The grade distribution may be changed by altering the break point for each letter grade

the graph at the bottom of the page shows the distribution of class grades

The grade distribution may also be altered by changing the proportional weight for any assessment on the Scores tab

Things to remember:

• This gradebook is set up for a maximum of 200 students, if you have more students it must be altered to accomodate more rows
• Grades are only calculated if there is a value for the assessment for the student on the Scores tab,
  AND the assessment has been marked for use,
  AND has a weight and appropriate point value associated with it
• Adding assessments to the Scores page should only be done by copying and pasting an existing column in order to replicate the required formulas
• At the end of term you can massage the data by altering the weights of different assessments in order to get an appropriate distribution of grades
• Do not put anything but numerical values in the areas to record grades on the Scores sheet or the Use, Weight and Points rows on the items tab
• Be careful when you insert or delete columns or rows... if you are trying something new, practice on a copy of your gradebook, because if the calculations are pushed altered, or moved out of place, they cease to function, and can be difficult to troubleshoot if you don't understand how they work
• This gradebook is set up to record grades for single score assessments, although three multipart sub-worksheets are included as examples. If you have a multiple part or repetitive grading structure or a more complex grading process based on intermediate calculations, the gradebook will need to be modified to work properly.