jump to navigation

#42-Determining Letter Grades from Course Averages in Excel®

November 05, 2004

LTA Overview

A member of the Microsoft® Office System, Microsoft® Excel is a very powerful spreadsheet program that allows users to organize, analyze, and communicate a wide variety of information. Previous LTA’s (LTA #3 and LTA #15) have explored ways that instructors can use Excel to manage class rosters and student grades.

LTA Credits

Michael L. Rodgers, Ph.D.
Professor of Chemistry
Southeast Missouri State University
Cape Girardeau, MO   USA 63701
mrodgers@semo.edu
http://cstl-csm.semo.edu/rodgers

However, Excel also offers several powerful logical and reference functions that can be used to automatically generate letter grades corresponding to students’ numerical scores for courses in which letter grades are assigned according to an absolute scale (e.g., 90 & up = A, 80 to 90 = B, 70 to 80 = C, etc.). This LTA explores two ways to obtain letter grades from numerical scores. While this LTA is tailored to the popular Excel environment, the same approach can be used in any spreadsheet software that supports a suite of functions or predefined formulas.

LTA Outcomes

Upon completion of this LTA, the user will be able to generate letter grades (A, B, C, …) from numerical information stored in a grade spreadsheet.

Skills Required to Complete this LTA

Users should be able to enter data and formulas into cells within a computer spreadsheet-based course gradebook. Familiarity with Microsoft® Excel is recommended, but not required.

Software and Hardware Required to Complete this LTA

Requirements are minimal: a general-purpose spreadsheet program installed on a computer. Microsoft® Excel is recommended, but not required. 

Procedure for Determining Letter Grades

Before letter grades can be determined, you will need to set up a spreadsheet with the following general characteristics:

For example, a class of three students might have a Midterm and a Final, from which an overall course average is calculated. The midterm counted 40%; the Final counted 60%:

Lta42.01

>Suppose that the course was offered Pass/Fail, with an Average greater than 50% required for a Pass (P). Milton and Shakespeare pass, but poor Kafka fails. We can program Excel to award the letter grades P/F using either of two methods.

A. The “Brute Force” Approach: using the IF function

The IF function allows us to apply a true/false test to the contents of a cell. The syntax is as follows:

 IF(logical_test,value_if_true,value_if_false)

We can therefore test Milton’s average score by placing the following function in Cell F3:

 IF(E4>50,"P","F")

The function tests the contents of cell E4, to see if Milton’s Average exceeds 50%. It does, so the value_if_true (P) will be placed in Cell F4:

Lta42.02

Excel allows the user to copy the formula in one cell to other cells. Doing this will produce letter grades for all students. Merely select Cell F4 by clicking on it. Copy the cell’s contents to the clipboard by selecting “Copy” from the Edit menu, or by pressing the Control-C keys. Then click on Cell F5 and drag to Cell F6 (the end of the roster). If you manipulated the mouse correctly, you should see the following formulas in the cells:

Cell F4 IF(E4>50,"P","F") Cell F5 IF(E5>50,"P","F") Cell F6 IF(E6>50,"P","F")

The spreadsheet will appear as follows:

Lta42.03

What about courses that use A, B, C, … grades? Suppose the grade scale for the course is as follows:

 90 -100 A  80 - 90 B  70 - 80 C  60 - 70 D   0 - 60 F

Obviously, Milton should get an A; Shakespeare a B, and Kafka an F. We would begin to write the function for Milton as follows:

 IF(E4>90,"A",value_if_false)

If Milton’s Average is >90, his letter grade is an A. But what value should be written if E4 is NOT >90? The letter grade could be B, C, D, or F. To account for these possibilities, we will NEST IF functions:

 IF(E4>90,"A",IF(E4>80, "B", IF(E4>70,"C",IF(E4>60,"D","F"))))

It looks complicated, but the idea is simple: IF(E4>80 is only invoked if E4 is NOT >90; IF(E4>70 is only invoked if E4 is NOT >80; IF(E4>60 is only invoked if E4 is NOT >70. The column headed “Grade2” shows the results of nested IF’s for all students:

Lta42.04-1
B. Using the HLOOKUP function: A More Elegant Approach

Nested IF functions work in many spreadsheet programs to give letter grades, but the formulas are prone to typing errors, and they don’t support easy modification of the numerical ranges that correspond to each letter grade. In Excel, the HLOOKUP function can determine grades by “looking up” the letter grade in a table. The formula’s syntax is

 HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

For this work, “Lookup_value” is the Cell in which the Average is located. “Table_array” is the lookup table in which the numerical ranges and letter grades are listed.

“Row_index_num” will be “2”, as Row 2 is the row in the table_array which contains the letter grade to be returned. We won’t use “range_lookup”.

To implement HLOOKUP, we must first construct a data table that contains both the numerical ranges and the letter grades. Then, we write the HLOOKUP function in the cell that will display the grade. For Pass/Fail grades, the lookup table – located in cells A10 through B11 – will have two columns, one for pass and one for fail. The first row contains the numerical ranges; the second row contains the letter grades:

Lta42.05

The formula for Milton’s grade is HLOOKUP(E4,$A$10:$B$11,2), located in Cell H4 within the column headed “Grade3”. Note that the table-array is $A$10:$B$11. Obviously, A10 to B11 is specified. But the $ signs are important: adding the $ signs fixes the table’s address to the A10:B11 range. Were the $ signs omitted, the table’s range would change whenever HLOOKUP is used for a different student.

Finally, the column headed “Grade4” uses the table_array in Cells A14 through E15 to assign letter grades:

Lta42.06
view full size image

As in the Pass/Fail case, the lowest value for each letter grade appears in the row above the letter, in order of increasing value from left to right. Milton’s grade in Cell I4 was assigned by the formula, HLOOKUP(E4,$A$14:$E$15,2). Click on the link to get the actual Excel spreadsheet used in these examples. Note how easy it is to change the range corresponding to a letter grade: you need only update the minimum value for the grade in Cells A14 to E14! For example, replacing the “90” in Cell E14 with “85” would change the range for an “A” from 90 – 100 to 85 – 100.

Comments»

No comments yet — be the first.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: