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

Let me revisit the very last thing I covered in the previous Tech Tip - dropping the lowest score. I worry that the explanation was spotty. Let me show the concept in a different way. I labeled some more columns as you can see in the screenshot below. Basically, I'm going to calculate each component of the "Average all but lowest score" formula in separate columns.



To calculate the sum of the homework, select the cell under the Sum column (as seen above), and type

=sum(

Next, 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, type

)

to close the formula and press the "Enter" key. You now have the Sum of all the homework assignments for this student. You can use copy and paste to copy this formula for the other students (discussed in previous Tech Tips).



Now, we go through a similar procedure for the Lowest column to find the lowest homework score. Select the cell in the Lowest column for the first student. Type

=min(

This is the "Minimum Value" function. It identifies the minimum value of a set of numbers. As with the sum, select the cells containing the homework grades, then type ")" and then press "Enter."



You can then copy and paste that cell for the other students in that column. You can see the result here, where the "Lowest" column has the lowest score for the homework assignment.



As you might guess, the function to find the maximum value of a set of numbers is "max."

The next column, "Num of HW" is an abbreviation for Number of Homework Assignments." In this column, we follow the same procedure, except we use the Count function. The Count function simply gives you the number of cells that have a number in them. In this case, we would expect it to be six. Type

=count(

then again select the cells with the homework grades.



And, like the ones above, you can copy and paste this cell for the other students.



Now, the final column involves constructing a formula from the other cells. Again, we want the formula

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

So, select the cell under HW Average (that is, the homework average with the lowest grade dropped) and type

=(

then select the cell under the Sum column for the first student, then type the "minus" sign, then select the cell under the Lowest column for the first student. Then close parenthesis: ")" and your formula should look like this.



Notice that Excel puts a colored box around each cell, and that those colors match the cell references in the formula. The cell reference is the combination of the letter of the column and the number of the row. So, the cell with the lowest homework grade for the first student is in cell J4.

Next, type

/(

since we are dividing and moving to the term in the denominator. Now, select the cell under "Num of HW" for the first student, then type

-1)

to finish the formula. It should look like the one below.



Press "Enter" to finish. You can copy and paste this cell to the other students.



The main point of going through this was to demonstrate the use of multiple functions built in to Excel (such as Min, Count, and Sum) and how to create a formula (to calculate the final column's value from other columns). We will proceed with our gradebook example in the next installment.