Lookup Table and Addressing

Initial Setup

Either:

  1. Copy the file gpa_teach.xls from V:\mgmt_110 to your Home drive H: or
  2. download it from here
  3. Fire up Excel 2000

Part 1

  1. Using the function wizzard
    We'll use a function which looks at a vertical table to convert letter grades to numeric values.

  2. In the course of this we'll see the importance of absolute cell addressing

  3. We'll use the VLOOKUP function to look up the value of grade A and put it into cell C23 and then copy to the other cells.

  4. Enter = into cell C23 to start a formula then pull down the formula bar and click on More functions ... Now scroll to the VLOOKUP function.

  5. Now you should see the VLOOKUP wizzard which has 4 things to enter:

    1. Lookup value: this is the cell with the grade you want to look up. Click the box on the right and then navigate to cell C23.

    2. Table Array: This is the range of the lookup table. Click the box and select H23:I33


    3. Col Index number: This is the column number of the table that has the value we're trying to get. In this case, it'd be column 2. If we had a table of Value : Grade it would be column 1.

    4. Range Lookup: The grades entered are discrete values so we want an exact match (we cannot have a grade of A 1/2 for example). Enter FALSE for exact match.

  6. Press Enter, and voila, ice le answer. But, watch what happens when you copy this formula down to the rest of the cells in the column.
Using a Lookup Table to convert letter grades to numbers
Course Grade Value Credits Grade Points Grade Value
International Cuisine and cooking A 4 3   A 4
Car Mechanics for left handers B- 2.67 4   A- 3.67
Social Implications of Molluscs C 2 3   B+ 3.33
Archnophobia in Prussian Empire B+ D26 #N/A
VLOOKUP(C26,H26:I36,2,FALSE)		 4   B 3
Old people's Activism A- #N/A
VLOOKUP(C27,H27:I37,2,FALSE)		 2   B- 2.67
C+ 2.33
GPA   C 2
C- 1.67
D+ 1.33
D 1
F 0

Fixing the problem.

7) Take a look at the formula in cell D26. It's giving a #NA error. Can you suss out why by looking at the formula?
Hint: Is the table range staying the same? Don't you want it to?
8) Let's use absolute addressing for the table range which puts "$" signs in front of column letters and row numbers, so $H$23:$I$33.
9) Now when we copy this to the other cells, the lookup table stays in the right place.
10) Calculate Grade Points = Value * Credits and put the sum in cell F29.
11) Sum Credits in E29
12) Grade Point Average is Sum Grade Points / Sum Credits.

Summary

Here's a graphic illustration of the correct completed VLOOKUP:

Classwork

A) Enter the four courses you are doing in the sheet below
B) Enter the grade you hope to get, and the credits for each course.
C) Calculate the value for each grade using the technique above.
D) Calculate Grade Points for each course and sum them. Sum the credits
E) Calculate GPA as above