You will use the excel spreadsheet gpa_teach.xls. The instructions are available inside the spreadsheetsheet itself or you can print them out from below.

Download the file gpa_teach.xls and save in your excel_data folder on your home drive.

Double-click on the file to start it up with MS Excel.

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 D23 and then copy to the other cells.

4. Enter = into cell D23 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.

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 for cells F23 - F27 (you know how to do this) and sum the Grade Points column in cell F29.

11. Sum the Credits column in E29

12. Calculate the Grade Point Average in F29 as Sum Grade Points / Sum Credits (you can work this one out)

View a graphic illustration of the correct completed VLOOKUP in a popup window.

1. Enter the four courses you are doing in the sheet below
2. Enter the grade you hope to get, and the credits for each course.
3. Calculate the value for each grade using the technique above. (Use the Grade: Value table H23:I33)
4. Calculate Grade Points for each course and sum them. Sum the credits
5. Calculate GPA as above
6. Save often.

Bar chart

1. Select range A2:C33 (include titles)
2. Use chart wizard and select simple bar chart.
3. Remove gridlines and place the legend at the bottom
4. type in Titles for the chart and X and Y axes.
5. Place chart in current sheet

Line chart

1. Repeat selection from step #1
2. Now choose the simple line option for chart type
3. Remove gridlines and place the legend at the bottom as before
4. type in Titles for the chart and X and Y axes.
5. Place chart in current sheet
6. Format Plot Area: remove border and area colour
7. Change the Y axis number to read "thousands", so instead of 1,600,000 you would have 1,600 and the caption "thousands" Hint: Format Axis: Scale - Display Units
8. Change the colour and thickness of the data lines on the chart.
9. On the X axis, align the year number so that it's 90 to the axis (ie vertical rather than slanted).
   Hint: Right-click on Axis and choose format. Select Alignment -> Orientation: move Text 90

Save and upload the completed spreadsheet into a new blog entry.