Tech Tip #4 – Making a Gradebook in Excel, Part 2

In this Tech Tip we’re going to continue creating a gradebook in Excel. We’ll go over the following points:

• Taking averages for a set of grades
• A note on blank cells and averages
• Dropping the lowest grade and then taking the average

First, let’s look at calculating an average homework score. Let's assume we have six homework assignments in our course, and that we want to average them so that the average will be one factor in the final grade.



So, we have a column after the last homework grade that we'll title "Average." I recommend that you create columns for all the homework assignments in the class first before creating the "Average" column. Select the cell in the Average column for the first student (as seen above). Then type

=average(

This will tell Excel to take the average of the numbers in all the cells we're about to give it. Your screen should look something like this:



Now, take the mouse, move the cursor over the cell with the grade for Homework 1 for the first student and hold down the left button (rather than just click). Then, still holding down the left button, drag the mouse so the selected area (the cells inside the box with the flashing line) encloses all the homework grade cells for the first student, whether or not there is a grade there.



Then, let go of the left button. Notice that even though there are no grades in the column for Homework 6, that cell must be selected as well for the final average to be correct.

Then, type

)

to close the parentheses and hit the "Enter" key.



Excel has calculated the average score for this student. You don't have to go through that process for every student, you can now just cut and paste that cell to tell Excel to perform that same calculation for all other students. Select the cell with the Average homework grade for the first student. Then, Copy that cell (one way is to go to the menu Edit, then select Copy).



The cell that has been selected for copying will now have an animated outline. Next, just as we selected multiple cells to create the average, select all the cells in the Average column from the second student to the last student. Your screen should look something like this (though the cell with the first student's average should still have the animated outline):



Now, use the Paste command to copy that first cell in the column to all the rest. (Edit menu-Paste works.) The result should look like this:



Excel has automatically calculated the Average for all students' homework assignments.

Important note: You will notice that Excel has only taken the average for cells where there is a number entered. That is, in the example above, Excel has only averaged homework grades 1-5 but not 6. Why? Excel ignores empty cells in taking averages, sums, etc. So, if you forget to enter a grade for a student that has not turned in his/her homework, that grade will actually be skipped in the calculation rather than included as a zero. If we fill in the homework 6 column with zeros, you see the averages change to reflect that homework 6 grades are now included.



So, when you create your gradebook, you need to decide if you want to fill all the grade cells with zeros to begin with or if you will just fill them in as you go. My recommendation is that you fill them all in with zeros (you can use copy and paste) to make sure you don't forget to enter a zero later when a student has not turned in an assignment. Let Excel remember this for you, rather than trust your memory.

Finally, we will look at how to have Excel drop the lowest grade from a set of grades. For example, you may have a policy that you will drop the lowest homework grade before you take the average. One mathematical formula to calculate this is here:

(Sum of all grades - Lowest grade) / (Number of grades - 1)

There are other ways to make this calculation, but this one lets us use three simple functions in Excel. If you know of a simpler way to do this, let me know.

To do this, I've created another column "Final Avg." in the spreadsheet. Select the cell in that column for the first student:



Then, type the formula below; except wherever you see the * symbol, select all the cells with homework grades for the first student.

=(SUM(*)-MIN(*))/(COUNT(*)-1)

In our example spreadsheet, the formula will look like this:

=(SUM(B4:G4)-MIN(B4:G4))/(COUNT(B4:G4)-1)

Where B4:G4 indicates where I've selected the six homework grades for the first student. You may want to copy and paste the formula above into Excel, and change the cell references to point to the correct place.

To understand what this formula does, compare it to the original typed-out formula above. In the numerator, we are taking the SUM of all the homework grades, and then subtracting out the lowest (which we find with the MIN function). In the denominator, we get the total number of grades using the COUNT function and subtract 1 from that (since we're dropping the lowest). If you make a mistake or just want to check the formula, you can select the cell where you typed it, and the formula will appear in the formula bar just above the spreadsheet.



Just as with the simple homework average above, you can copy this cell for the first student to get the calculated value for all other students in this column.



If you have questions, please leave a comment or contact me.

In the next part of this series, we'll go over how to calculate final grades for the class.