The Ultimate Gradebook - a tutorial

We begin with basic data as it comes to us at the college. Here is the link to the gradebook.

This data is pasted into Excel and comes into a single column. Our first step is to parse this information into columns. For this we use the

Data -> Text to Columns

command. Select this option from the menu bar of Excel as shown below.

The conversion menu looks as follows. Be sure to check the "delimited" box.

Then choose the Next button. There you can select which character(s) are the delimiters. In the present case we will select the Space, which is entered in the Other position and the Comma.

At this time choose Finish. The finished spread sheet will look like the following

We are now ready to insert grades. Let's do that with 3 exams, a final, and 14 quizzes. It is easy to make the headings Q1, Q2, and so on using the Excel automatic formating feature. Simple type Q1 into the first cell. Then selecting the small square at the lower right of the cell, drag it across the columns. The new heading will be automatically created. This is shown in the two pictures below.

So, we have column headings headings, grades entered, and now we want to compute averages and grades. This can all be done within Excel.

Tasks:
1. Curve Exam 2 by four points.
2. Average all three exams.
3. Average the quizzes two different ways.
4. Compute the total score.
5. Make the grade breaks and compute the letter grades.
6. Compute the grade point ratio.

Task 1. Curve Exam 2 by four points.
The simplest way to do this is to insert a new column and use the formula. This is shown in the pictures below. Note the formula is defined by the "=" (equal) sign. When Excel sees the equal sign as the first character, it assumes a formula is to follow. If you insert Space + "=", then you will see the equal sign in the cell.

Now copy the cell into the remaining cells, either by using the method above or by copying the cell and then selecting the remaining cells and pasting. A second way to curve the exam by four points is to add two new columns, and in the first column insert all fours, and then add the curve column to the exam grade to get the curved exam grade. This is shown below in the first column. The command we actually used is shown in the second column. .

Be sure the copy and paste the formula to the remaining cells. You will note that in our spreadsheet we have one student being exempt from this exam. In this case, the curve was not added.

Now to confuse things properly, there are at least two more ways to add in the curve. We'll see how to do one of them below, in another context.

At this time, except for the fact that the exam average has been compute, our grade book looks like this

Task 2. Average all three exams.
On the face of it, this is easy. You can either sum the three values and divide by three or use the average function. We used the second method for a particular reason. First the syntax is

The reason for using this command is that the exempt exam is not included in the students average. His/or her average is not included in the Exam average. Specifically, we used the command =AVERAGE(J2,M2,N2) . Note that CHO's average is just that of the two exams he took.

Task 3. Average the quizzes two different ways.

If you want to average the quizzes, just apply either of the methods discussed above. In my classes, I usually deduct the lowest two quiz scores and average the remaining. If I wanted to deduct just the lowest score, there is a traditial way to do this. Use the MIN command. The syntax is easy: MIN(Range) gives the smallest numerical value over the given range. But to remove the lowest two scores is not so simple unless you use the command SMALL which has syntax

So, MIN(Range) = SMALL(Range,1). In our example, we compute the two averages as follows.

Removing just the lowest score

Removing the two lowest scores

You may look at the formulas above with a little suspicion. We explain. First of all, we use SUM instead of average because we want to pick up all quizzes, and take no grades as zeros. Notice that we sum over the entire range of quizzes, which runs from cell U2 to cell AH2. Then we subtract the smallest of them. But what is that we divide by. Here we used the command COUNTA, which counts the non empty cells over the range given, and for the range we selected the headings of the quizzes. This fanciness could have been replaced by the actual number, 14. Of course we need to subtract one or two depending on how many quizzes we are deducting.

Finally you note the use of the dollar signs ($) all over the place. When you place a dollar sign before a cell name, it will not change when performing the copy and paste functions. This way we use the same count for each of the students grades after this process.

4. Compute the total score.
The total score is computed with the following formula "=3*O2+P2+R2" Why? Well, cell O2 contains the exam average, which is multiplied by three to get the sum of the total exam scores. Then we add cell P2, which is the Final exam scoree and finally we add the Quiz average in cell R2, the one with the lowest two quizzes removed.

5. Make the grade breaks and compute the letter grades.
First make the grade breaks. These are the values where the grade changes. Our table is shown below. For example we will give anyone with 500 or more points an "A", anyone with 460 points or more a "B", and so on.

The command, again, if the IF operation. Here, we've used it in a tricky way. Recall the syntax "IF(Range, Ture, False)." In the False case we insert another if command, and in this way we cascade down the list of available grades. The final alternative, you will note, is the grade "F". This mean that the total grade failed all of the tests for a higher grade. Here are a couple of other things to note:

• We used the dollar sign ($) notation, so that we will always use the same grade breaks for each of the students.
• We use the ">=" notation so that the break itself will give the higher grade.
• There are multiple right parentheses at the end of the formula, closing all the IF's.
• If any of the grade breaks are changed, the spreadsheet immediately updates itself.

6. Compute the grade point ratio.
Computing the grade point ratio is accomplished in two steps. The first step is to compute the number of A's, B's, C's, D's, and F's. This is done with the COUNTIF command. This is just like the count command, except that it is conditioned.

The range in =COUNTIF($T$2:$T$16,"A"), from cells T2 to T16 contains the range of the previously computed grades. Formally, the syntax is

Finally, the grade point ratio is computed using the usual formula, (4 x number of As + 3 x number of Bs + 2 x number of Cs + number of Ds)/(total count) . For our spreadsheet, this formula is shown below

The Excel gradebook:

©2003 G. Donald Allen