Excel 2010 Bible

Part II: Working with Formulas and Functions

322

Determining letter grades for test scores ..................................................................

A common use of a lookup table is to assign letter grades for test scores. Figure 14.10 shows a

worksheet with student test scores. The range E2:F6 (named GradeList) displays a lookup table

used to assign a letter grade to a test score.

Column C contains formulas that use the VLOOKUP function and the lookup table to assign a

grade based on the score in column B. The formula in cell C2, for example, is

=VLOOKUP(B2,GradeList,2)

When the lookup table is small (as in the example shown earlier in Figure 14.10), you can use a

literal array in place of the lookup table. The formula that follows, for example, returns a letter

grade without using a lookup table. Rather, the information in the lookup table is hard-coded into

an array. See Chapter 17 for more information about arrays.

=VLOOKUP(B2,{0,”F”;40,”D”;70,”C”;80,”B”;90,”A”},2)

Another approach, which uses a more legible formula, is to use the LOOKUP function with two

array arguments:

=LOOKUP(B2,{0,40,70,80,90},{“F”,”D”,”C”,”B”,”A”})

FIGURE 14.10

Looking up letter grades for test scores.

Calculating a grade-point average

A student’s grade-point average (GPA) is a numerical measure of the average grade received for

classes taken. This discussion assumes a letter grade system, in which each letter grade is assigned

a numeric value (A=4, B=3, C=2, D=1, and F=0). The GPA comprises an average of the numeric