Tech Tip #6 – Making a Gradebook in Excel, Part 3

We continue creating a gradebook with this Tech Tip. This time, we will cover:

• Using a formula to calculate the final grade
• Automatically calculating and assigning a letter grade

Here is our spreadsheet with some more grades filled in. We have 6 homework assignments (averaged and lowest score dropped), four tests (to average), and one project final grade. With what we have learned so far, I assume you will be able to re-create this table, or use the principles to develop one for your class. I also assume you will be able to fill in the "T Avg" column with a formula to average the Test scores - the procedure is very similar to that covered in Tech Tip #4.



Now, let's assume that the final grade for the course follows this formula:

25% = Homework average
40% = Tests average
35% = Project grade

Since these percentages are not all equal, we can't just average these three numbers for each student. We also have the complication that the Homework average is a score out of 10 possible points, while the Test average and Project grade are scores out of 100 possible points. As with many problems like this, it may be best to write out a formula and THEN put it into Excel. We will use the following formula:

Final Grade = (Homework Avg. * 10 * 0.25) + (Tests average * 0.4) + (Project grade * 0.35)

As you see, we take each term and multiply it by its respective percentage, then add them together. Also, in the case of the Homework average, we multiply by 10 to convert it from a 10 point scale to a 100 point scale in the formula.

To do this in Excel, select the cell in the "Final Grade" column for the first student. Next, type

=(

Then click on the cell with the Homework Average for the first student. Then type

*10*0.25)+(

Next, click on the cell with the Text Average for the first student. Then type

*0.4)+(

then click on the cell with the Project grade for the first student. Then type

*0.35)

Your formula should look something like this



Now, press the "Enter" key. The final grade will appear for this student. You can copy and paste this formula for the other students to calculate their final grades. Our gradebook with final grades is below:



We'll cover one more topic: how to automatically assign letter grades in Excel. This involves the "Lookup" function and a few other features of Excel. First, I will Hide the columns with the Homework, Test, and Project grades to focus on the final grade. To do this, take the mouse and hold down the left mouse button over the column header - that is the grey/tan colored cell with the letter identifying the column. It will look something like the below picture - and the entire column will appear selected.



Then, drag the mouse over all the columns you wish to hide. In this case, we will hide all the grades except the Final Grade column.



Then, while the columns are selected, right-click on any of the selected cells to bring up a menu - select Hide from this menu.



This will Hide the selected cells. We'll look at how to Unhide them later in this Tech Tip.

Now, we need to set up the criteria to select between grades. I suggest you use a different worksheet for this. You see the tabs near the bottom of the screen. Right now we're working in "Sheet 1" but we have the other two available.



You can add or delete sheets from your spreadsheet as you wish, and reference information between them. You can also rename these sheets to something more logical. Right-click on the sheet name (e.g., "Sheet 1") and select Rename. Then, you can type what you want. I have renamed two tabs as seen below.



Click on a tab to switch to it. In the Letter Grades tab I've set up the following.



In the first column, I have typed a list of letter grades in ASCENDING order. The order is very important as this is how the LOOKUP function works. In the second column, I have typed the LOWEST grade that will qualify for the corresponding letter grade. This is also important as this is how the LOOKUP function works. These are only examples - type the numbers that you have selected for your course. The table above corresponds to the following scale:

A+: 97-infinity
A: 94-96.99
A-: 90-93.99
B+: 87-89.99
B: 84-86.99
B-: 80-83.99
etc.

To emphasize, there are two essential aspects of creating this table of values: they must be ASCENDING order and you must use the LOWEST value for each grade level.

With this table, we can use the Lookup function to assign a letter grade. The Lookup function takes three inputs. First, it looks at a particular value we want examined. In this case, it will be the "Final Grade." Then, it "looks up" that value from a range - in this case, the "Score" column in the "Letter Grades" sheet. Finally, we tell the Lookup function a corresponding range of values to the first range, from which to draw the value to place in the cell. In this case, that will be the "Letter Grade" column.

To implement the Lookup function here, first select the cell under the "Letter Grade" column on the sheet with the Student's Grades.



Then type

=Lookup(

Then select the cell under "Final Grade" for this student. Then, type a comma. Next, switch to the "Letter Grades" sheet by clicking on its tab at the bottom. Select the cells that represent the Lowest scores for each letter grade.



Type another comma. Now select the cells with the letter grades. When you have those selected, type the close parenthesis: ) and then hit the Enter key. You should now see the letter grade in the "Letter Grade" column for the student. If you need to double-check the formula, it should look something like this, though your cell references may be different.



You CAN NOT copy and paste this immediately for the other students. First, you have to "freeze" the references to the the columns in the "Letter Grades" sheet. If you do not do this, they will not be properly referenced. Hopefully, I will cover the reasons for this in a future Tech Tip - trust me for now.

To freeze these references, put "Dollar signs" in front of the column and row references in the formula. To edit a formula, click on the cell with the formula you want to edit, and it will appear in the Formula bar for you to edit (seen below) which is typically right above the spreadsheet.



Notice how there are now dollar signs before the letters and numbers of all cells referenced in the "Letter Grades" sheet. Do NOT freeze the cell of the Final Grade for the student.

Once you have done this (and double checked that it is similar to the formula above), you can copy and paste the cell in the Letter Grade column for all students.



To Unhide the hidden cells, select the columns on either side of the hidden cells, right-click to bring up the menu, and select Unhide.



And now, this post has become quite long enough. In our next "episode" we will look at calculating useful statistics on students' grades.