Topnotch Gradebook Manual

You may want to download and take a look at Topnotch Gradebook first before reading this manual. If you have not yet purchased a license, you can obtain a demo version on the Downloads page.

Index

Working with Spreadsheets
--Frozen Window Panes
--Turning off AutoComplete
--Using "Paste Special..."
--Excel Help Links
The Worksheets
--Intro
--Scale
--Setup
--Master (Includes column explanations)
--BC1 & BC2
--Class_Report
--Student_Report
Tips & Examples
--Setting Up Categories
--Calculation Methods
--Weighting Your Categories
--Combining Categories
--Handling Extra Credit
--Grading on a Curve
--Exporting Your Data
--Emailing Grades
Error Codes

Working with Spreadsheets

Frozen Window Panes

Several of the worksheet windows are in "fozen" mode. This means that when you scroll to the right (e.g., in the Master sheet) the students' names will remain visible on the left side of the screen. Likewise, when you scroll down, the assignment details at the top of the sheet will also remain visible. This can create the impression that some data has disappeared, but you only need to scroll up or to the left again (with the arrow keys) for it to reappear.

Turning off AutoComplete

Many spreadsheet programs, like Microsoft Excel, come pre-configured in such a way that they will try to guess your keystrokes before you finish entering information. This can be a real problem when entering grades. For example, if you enter an A, you probably don't want your "smart" spreadsheet program to change it immediately to an A-. To disable this function in Excel on a Windows computer, click on "Tools > Options" on the main menu bar, then select the Edit tab. Now find the option that says "Enable AutoComplete for cell values" and de-select it. On a Mac, this option can be found under "Excel > Preferences > Edit." In OpenOffice.org, click on "Tools > Cell Contents..." and de-select "AutoInput."

Using "Paste Special..."

Another thing to know when working with spreadsheets is that the Paste function (as in "Copy & Paste") does not always work as expected. It is usually best to use the "Paste Special..." function instead of simply Paste when pasting into a spreadsheet. On the menu bar click on "Edit > Paste Special..." and then select "Values" only. In OpenOffice.org, select "Strings" and "Numbers" only.

Excel Help Links

For detailed help on using Excel, we recommend Vertex42's Excel Nexus.

The Worksheets

The following is an introduction to the worksheets found in Topnotch Gradebook.

Intro Sheet

This sheet contains basic information you need to know before getting started. It also contains the End-User License Agreement (EULA), which you must agree to before using the gradebook.

Scale Sheet

The Scale sheet allows you to configure the grading scale that is to be used for the class. If you do not intend to use plus or minus grades, you can just delete them and their associated thresholds from the table. You can also set up special codes on this sheet to be equal to specific values. For example, if you want to have a special code that equals "90%", which is usually exactly halfway between an A- and a B+, you can set it up here.

Setup Sheet

This is the main sheet for setting up parameters for the class. Enter the course title, teacher's name, and other such information at the top of the sheet. Next, you can decide whether you want to round the final grades to the nearest whole percent or to a tenth of a percent. Finally, on this sheet you can set up the categories and decide how you want the grades to be calculated. If you find this part challenging, please read the Tips section, below.

Master Sheet

The Master sheet, as the name suggests, is where all the heavy lifting for grade calculations occurs. Thankfully, the spreadsheet does it all for you!

To get started, just enter the students' names in the appropriate column. You can also enter their Student IDs if you wish.

The next columns on the Master sheet are "Current %" and "Current Grade." As you proceed, the gradebook will automatically keep a running display of the students' overall grades in these columns.

The next column is labeled "Shortfall." As grades are earned, this column will display how far away each student is from the next letter grade cutoff.

Next, you will see a column for "Final Grade." This is a place for you to record the final grade that you actually give to each student--in case it differs from what the gradebook computes in the Current Grade column.

The next set of columns come in groups of three and are for entering assignment grades. For each assignment, you can type in a title at the top of the page (yes, you can over-write the number that appears there). Next, select the category it belongs to from the drop-down box. The list of categories in the drop-down will depend on what you entered on the Setup sheet. If you are using points to grade the assignment, there is also a cell for entering the number of points that are possible.

Once you have the assingment information set, you can enter grades in the "Input" column, either as points or as letter grades. If everything is setup correctly, the gradebook will display the percentage under the "%" column and the letter grade under the "Grade" column.

Following the assignment columns on the Master sheet, the Full Edition has space for two bonus grades. These are for situations in which you want to increase the final grades by a certain number of percentage points, but only for students who complete the extra credit assignments.

Following the sections for the assignment and bonus grades, there is a section with columns that display the current grades students are earning in each category. If you wish to manually override any of these grades, there are cells in this section for you to enter adjustments. Please note, however, that any values entered here will continue to override any grades that may be earned later on in that category. If you wish to perform "What If" scenarios, you may find the Student_Report sheet to be a safer option.

Finally, to the very far right side of the Master sheet, there is a section for entering students' email addresses and any comments you may want to keep.

BC1 & BC2 Sheets

These sheets only appear in the Full Edition. If you are using the Full Edition, you will want to use these for your largest categories. For these to be active, make sure you assign categories to them on the Setup sheet in the rows marked for BC1 or BC2. Then, enter the students' names on the sheet you wish to use exactly as they appear on the Master sheet (you can use Copy & Paste Special for this). Next enter the names of the assignments, the points possible, if any, and the grades earned just as you would on the Master sheet. Please note that the BC1 sheet will hold up to 50 assignments while the BC2 sheet will hold up to 30.

Class_Report Sheet

This sheet is included to give you overall picture of how a class is doing. There is nothing to edit on this page, except to choose which kind of grades you wish to view from the drop-down list. You can choose to view grades in each category either as percents, points, letter grades, 4-point scale equivalents, Z-scores, or T-scores. At the bottom of this page there is also a table that shows how many students earned each grade level in each category.

Student_Report Sheet

The Student_Report sheet is a handy way to view all the grades that have been earned by a particular student. Just select the student's name from the drop-down list (which is linked to the Master sheet) and you can get a snapshot of how she or he is doing. This sheet is already formatted for easy printing. Also, some teachers find it useful to copy a part of this sheet by highlighting it with a mouse and then clicking Edit > Copy on the main menu. Then you can paste it directly into another document, or even into the Compose screen of some modern email applications, such as Microsoft Outlook.

Tips and Examples

Below are some ideas for different ways to set up and use Topnotch Gradebook.

Setting Up Categories

With Topnotch Gradebook, every class must have at least one category. In the Light Edition, the assignment grades for all 8 categories will be entered on the Master sheet, which holds up to 25 grades. In the Full Edition, the Master sheet will hold grades for the first 6 categories (also 25 total) and then the BC1 and BC2 sheets will hold the grades for the remaining 2 categories. If you are using the Full Edition, and you expect to have more than 25 total assignments, you will want to setup your gradebook so that your largest categories will be linked to the BC1 and BC2 sheets. For example, if you are going to have 30 quiz grades, then you may want to put "Quizzes" on the row for BC1 on the Setup sheet.

If you don't usually separate your assignments into categories, it is perfectly OK to setup just one category and to call it something like "Everything." If you are using the Full Edition, you may even want to put all of your grades on the BC1 sheet, because it will hold more than the Master sheet. Please note, though, that even if you don't use any of the 6 categories that are linked to the Master sheet, you still will need to enter the students' names on the Master sheet as well as on the BC1 or BC2 sheets.

Some teachers do not give percent weights to their categories, but rather prefer to calculate grades by simply adding up all the points students have earned at the end of the term and dividing that by the total number of points possible. If this is how you calculate grades, then you will want to select the "Total Points" method of calculation for each of your categories and also select the "Auto-weight based on points possible" option for weighting your categories.

Calculation Methods

For each category, you can choose whether you want the overall category grade to be calculated using the "Average Grade" method or the "Total Points" method. The difference is subtle, but could have an effect on the final grades. The following example will help clarify the difference. Say a student earns the following grades in a category called "Quizzes":

Quiz 1: 5 out of 7
Quiz 2: 2 out of 3
Quiz 3: 9 out of 10
Quiz 4: 7 out of 10
Quiz 5: 2 out of 5

Here's how the calculations would work using the Average Grade method:
Quiz 1: 5/7 = 71.4%
Quiz 2: 2/3 = 66.7%
Quiz 3: 9/10 = 90%
Quiz 4: 7/10 = 70%
Quiz 5: 2/5 = 40%
Average = (71.4 + 66.7 + 90 + 70 + 40) / 5
Average = 67.6%, which would be a D+ on the default scale.

Now, here's how the calculations would work using the Total Points method:
Total Points Earned = (5 + 2 + 9 + 7 + 2) = 25
Total Points Possible = (7 + 3 + 10 + 10 + 5) = 35
Overall Category Grade = 25/35 = 71.4%, which would be a C- on the default scale.

Using one method, then, the student would earn a D+, while using the other she would earn a C-. As in this example, the two methods generate different results whenever the assignments in the category have different amounts of points possible. If there is only one assignment in a category, or if all the assignments have the same amount of points possible, there will be no difference between the results of the two methods.

Weighting Your Categories

Topnotch Gradebook gives you two options for weighting your categories. You can either enter weights as percents on the Setup sheet or direct the gradebook to auto-weight based on the points possible. If at the end of the term you usually simply add up all the points earned and divide that by the total points possible, then you may want to choose option (b), "Auto-weight based on points possible." Here's how it works:

Say you have 3 categories as follows:
Quizzes: 600 points possible
Exams: 300 points possible
Homework: 100 points possible
Total points possible = 1000

Then, if you choose option (b) to auto-weight, the categories will be weighted as follows:
Quizzes: 600/1000 = 60%
Exams: 300/1000 = 30%
Homework: 100/1000 = 10%

If you only have one category, you can choose either method and the results will be the same.

Combining Categories

If you need more than 8 categories, consider combining some of the them. For example, if you have a category called "Midterm" that's worth 10% of the final grade and another one called "Final Exam" that's also worth 10% and each have just one assignment in them, then you can combine them into one category called "Exams" using the Average Grade method and set it to be worth 20%.

Only combine categories that use the same calculation method. When combining categories, it is also important to ensure that doing so will not alter the students' final grades. To determine whether or not it will, a number of factors must be considered, including the calculation method and the chosen option for weighting the categories. In most cases, two categories can be combined when a certain ratio drawn from the first category equals a ratio drawn from the second category. The table below shows which ratios to compare under different scenarios.

When is it OK to combine categories?

	Average Grade Method	Total Points Method
Using manual weighting ("Use the weights I enter below")	Only OK if w₁/n₁=w₂/n₂	Only OK if w₁/p₁=w₂/p₂
Using auto-weighting ("Auto-weight based on points possible")	Only OK if p₁/n₁=p₂/n₂	Always OK

Key:
n₁ = Number of assignments in the first category
n₂ = Number of assignments in the second category
p₁ = Sum of all the points possible in the first category
p₂ = Sum of all the points possible in the second category
w₁ = Weight of the first category
w₂ = Weight of the second category

Handling Extra Credit

There are several ways to handle extra credit with Topnotch Gradebook.

You can use the Bonus Grades columns (Full Edition only). If you are using the Full Edition, you will find two sets of columns for bonus assignments on the right-hand side of the Master sheet, following the assignment grade columns. Grades entered here will generate percentage points that will be added directly to the students' final grades. For example, if a student gets 10 points out of 10 possible on a bonus assignment (100%), and you have set the weight for this assignment to 5%, then that student will get 5% added on to his or her final grade.
You can give extra points on an individual assignment. For example, you can give 5 bonus points on an exam that's worth 100 points. If a student gets them all right, he or she will get 105% on that assignment.
You can make an assignment optional. Remember that empty cells do not count against a student. If you leave a cell blank, then the assignment will not be included in any of the calculations for that student. So, if you want the assignment to be optional, you can enter grades only for students who turn something in and leave the rest blank. You can also enter an excused code (defined on the Scale sheet) for students who don't turn in the extra credit. Please note that if a student's grade on the optional assignment is lower than the grade he or she was already getting in that category, then recording it could hurt rather than help the student.
You can create an extra credit category. If you are using manual weighting (i.e., if you have selected "Use the weights I enter below" on the Setup sheet) it is OK to have more than 100% total weights. For example, if you have 5 categories that are each worth 20%, you can still add a 6th for extra credit and make it be worth, say, 10%. Students who do not turn in anything for the extra credit category will have their grades calculated on the basis of the total weights equaling 100%. Students who do turn something in for the extra credit category will have their grades calculated on the basis of the total weights equaling 110%. Please note that this method will recalibrate the relative weights of the categories for the students who turn in extra credit. In this example, the "real weight" of the first 5 categories would be 20%/110%, which equals about 18.2%. The "real weight" of the extra credit category would be 10%/110%, which equals about 9.1%

Grading on a Curve

If you have analyzed your grades and have decided upon a curved scale for your class, you can use that scale to adjust any set of grades in the gradebook.

For an individual assignment, you should first set your scale and assign letter grades for each student. Then, enter the curved letter grades into the gradebook rather than the raw point-scores. Alternatively, you can enter the raw point-scores into the gradebook first in order to calculate the average and the standard deviation, which may be useful for setting the curve. Then, once the curve is set, you can change the students' grades from the raw point-scores to the appropriate curved letter grades.

It is also possible to apply a curved scale to an entire category. On the Master sheet, there is a section to the far right for overriding category grades. Once you have decided on a curved scale for a particular category, you can enter the appropriate letter grades from the curve in this section. For setting the curve, you may find it useful to view the students' Z-scores and T-scores on the Class_Report sheet.

Exporting Your Data

Since Topnotch Gradebook is entirely contained in a spreadsheet file, exporting your data is a cinch. All you need to do is highlight the cells containing the data you want to export, copy it and paste it into another document. For example, you can copy the entire table from the Class_Report sheet and then paste it into a new, blank spreadsheet document.

Tip: Click on the first cell in the top-left corner of the table you want to copy. Then hold the shift key down while using the arrow buttons on your keyboard instead of your mouse to highlight the rest of the table. Click "Edit > Copy" on the menu. Now, open a new spreadsheet file, click on cell A1, and then click "Edit > Paste Special..." (see Using "Paste Special..."). Once you have the data in a new spreadsheet, you can format it as you need to in order to prepare it to be imported into another program.

Emailing Grades

To email a student's grades, you can select his or her name on the Student_Report sheet, then copy the cells you want (e.g., A7:I25, which is a named range called "studentreport") and paste it into another document that can be attached to an email message. See the section above on Exporting Your Data for help on how to do this.

You can paste the table into another spreadsheet, a Word document, or even directly into the Compose screen of many email applications, like Microsoft Outlook and Mozilla Thunderbird.

Error Codes

The following are explanations of the error codes that Topnotch Gradebook may generate.

cat wt

The selected category does not yet have a weight associated with it. Navigate to the Setup sheet and make sure all categories have been given a weight. This code will only appear if you select "Use the weights I enter below" on the Setup sheet.

cat?

A category has not yet been selected for a particular assignment. Use the drop-down list at the top of the column to choose a category. Categories can be configured on the Setup sheet.

EULA

The user has not yet accepted to terms of the End-Use License agreement on the Intro sheet. Put a check-mark in the check-box at the bottom of the Inro sheet if you accept the terms of the agreement.

name?

For the Full Edition only: a name has not yet been entered for a student on either the BC1 or the BC2 sheet. Grades are linked according to students' names. If grades are entered on the BC1 or BC2 sheets, but no name or a name that does not exactly match any of the names on the Master sheet is entered, this error code will appear.

nolink

For the Full Edition only: a student's name could not be matched to any of the names on the BC1 or BC2 sheets. This error code will only appear if some names have already been entered on the BC1 or BC2 sheets, but not all the names on the Master sheet can be matched. If you do not intend to use one of these sheets, make sure all the cells in the Name column are empty. If you do intend to use the sheet, make sure the names match exactly to the names on the Master sheet.

poss?

The number of points possible has not yet been defined for the assignment. Enter a number in the Poss cell at the top of the column for the assignment, or else change the setup parameters. There are three scenarios in which this error code will appear when no points possible have been defined:

A number is entered in the Input column.
"Auto-weight based on points possible" has been selected on the Setup sheet for the weighting of categories.
"Total Points" has been selected for the calculation method of the selected category.