Excel 2010 Bible

Chapter 14: Creating Formulas That Look Up Values

323

grade values weighted by the credit hours of the course. A one-hour course, for example, receives

less weight than a three-hour course. The GPA ranges from 0 (all Fs) to 4.00 (all As).

Figure 14.11 shows a worksheet with information for a student. This student took five courses, for

a total of 13 credit hours. Range B2:B6 is named CreditHours. The grades for each course appear in

column C. (Range C2:C6 is named Grades.) Column D uses a lookup formula to calculate the

grade value for each course. The lookup formula in cell D2, for example, follows. This formula

uses the lookup table in G2:H6 (named GradeTable).

=VLOOKUP(C2,GradeTable,2,FALSE)

FIGURE 14.11

Using multiple formulas to calculate a GPA.

Formulas in column E calculate the weighted values. The formula in cell E2 is

=D2*B2

Cell B8 computes the GPA by using the following formula:

=SUM(E2:E6)/SUM(B2:B6)

The preceding formulas work fine, but you can streamline the GPA calculation quite a bit. In fact,

you can use a single array formula to make this calculation and avoid using the lookup table and

the formulas in columns D and E. This array formula does the job:

{=SUM((MATCH(Grades,{“F”,”D”,”C”,”B”,”A”},0)-1)*CreditHours)

/SUM(CreditHours)}

Performing a two-way lookup .................................................................................

Figure 14.12 shows a worksheet with a table that displays product sales by month. To retrieve

sales for a particular month and product, the user enters a month in cell B1 and a product name in

cell B2.