Teaching

Online Grade Books

Automatically Generate Letter Grades

It is possible to use IF-statements in a formula to calculate and assign letter grades to students based on a semester percentage; however, it is simpler to use a VLOOKUP table. The template (above) includes a VLOOKUP table, which uses the syntax:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

where all you really need are the first three operators. Lookup_value refers to the cell where the semester percentage can be found. The table_array is the "name" of the table (you will have to highlight all the cells of the table, then just above A1 you will see a spot in Excel to "name" the region you've highlighted. In the case of the example above, the table is named "grades.") The col_index_num means that the formula will look to that column (in the case of the example, the second column) as the "output" of the formula, the item to be deposited where you want the letter grade to show up.

Dropping the Lowest Test Score (Or possibly dropping the final exam): the MIN function

To drop just one test score, first add up all the tests and then subtract the lowest from that "range". This would look like: =(Test1+Test2+Test3+Test4)-MIN(Test1, Test2, Test3, Test4). Simply substitute the cell number for each test, and you will have the "best three" tests added together in that formula.

Dropping More Than One Lowest Test Scores: the SMALL function

To drop more than one test score, use the SMALL function to isolate the "second-smallest" or "third-smallest" number. The formula =SMALL(K1:Q1, 2) provides the "second-smallest" score in the range of cells K1 through Q1. As this only isolates what the second-smallest number is, this would need to be part of a larger formula (for instance, it could be added to the MIN function as shown above to also drop the second-smallest score).

Automating "Whichever is Higher": the MAX function

Like the MIN function, the MAX function looks between two cells and "returns" whichever is the higher of the two (it can also look for the highest score in a whole range, rather than just comparing two cells). The syntax is simply =MAX(K2, L2) for two cells K2 and L2, or you can add more cells separated by commas. This formula is often used as part of a larger formula.

Choosing NC vs. F grades: the MAX function

The MAX function can be very helpful in determining whether a student deserves an NC grade rather than F. See the mathematics gradebook (below) for an example.

Keeping the Spreadsheet Current "As of Today": the COUNT function

The COUNT function simply adds up how many cells are populated with content in a given range, with the syntax =COUNT(F2:M2) to search from F2 through M2. Your formula could make use of this to provide an ongoing, "as of today" grade for your students. This would be especially useful for grades that are ongoing, such as homework or tests, but will not apply to onetime events late in the semester, such as a project or paper.

Example of SMALL, COUNT, and MAX

You can make use of SMALL to drop more than one score, COUNT to provide a score "as of today," and MAX to determine if the appropriate grade should be NC rather than F.