Tech Tip #7 - Making a Gradebook in Excel, Part 4

We've looked at the main things you can do to get a working gradebook for your course. In this Tech Tip, we'll cover how to calculate some interesting statistics on the grades for your information and/or to share with your class. While 45.7% of all statistics are made up and not useful (Yes, I just made that up), they can help you see trends in grades to help guide your teaching.

In the gradebook we've made so far, each row has grades for a single student, while each column holds the grade for an individual assignment. Let's calculate a few statistics on the homework grades. We'll start with averages, lowest scores, and highest scores. We've covered the first two before, so this should be review.

Let's first indicate where we are going to calculate these statistics. I typically go down to the last student in my list, skip two rows, then put in what statistic I am going to calculate.



The space is to separate the statistics from the students' grades so I don't confuse the numbers. I also make the text bold to make them stand out as different. Let's calculate the average, lowest, and highest values for Homework 1. First, select the cell in the row for "Average" and column for Homework 1. Then type

=average(

and select the homework 1 grades for all the students. Then type the close parenthesis and hit the "Enter" key. Before you hit Enter, you should see something like this:



Now, we can do the same thing for the Lowest value, except putting it in the row by "Lowest" and using the "Min" function rather than "Average." Your formula will look like this:



We can repeat the procedure again, this time in the "Highest" row using the "Max" function. Your formula will look like this:



You may notice the green triangle in the top left corner of each cell. This indicates that Excel thinks there is a potential error in what you did. To see what Excel thinks is wrong, click on one of the cells with the green triangle. You will see a square with an exclamation point appear to the left of the cell.



Slowly move your cursor over this box. It will turn an orange-ish color and another box with text may appear with the error message.



Click on the downward arrow that appears to see your options.



Basically, Excel doesn't like the extra rows of spacing we put in between the last student and where we're calculating the statistics. It thinks we probably wanted to select all cells down to right above where we are calculating the average. In this case, we want to "Ignore Error" since we don't want Excel to change anything. This is typically the safest option, but Excel may have a good suggestion every now and then so it is worth looking at the messages. You will notice that if we select "Ignore Error" then the green triangle disappears.



We can copy and paste these statistics to the other columns of the homework grades. Select the three cells with our statistics for Homework 1.



Then, Copy the cells, either by using the menu (Edit-Copy), or right clicking on the selected cells and selecting "Copy," or by using the Copy button in the toolbar.

At this point, there are a few different ways to copy this group of cells to the right places. I'm going to show you the one I tend to use. Select all the cells in the Average row for Homework grades 2 through 6.



You only need to select that first row of cells. Excel will realize that it should copy the cells for Lowest and Highest grades beneath. Then, Paste the cells, either by using the menu (Edit-Paste), or right clicking on the selected cells and selecting Paste, or by using the Paste button in the toolbar.



I'll go over a few more functions that might be useful for statistical analysis of grades. First, you can get the standard deviation using the "Stdev" function.



The first and third Quartiles can be calculated with the "Quartile" function. The Quartile function takes two inputs: the range of numbers you want to examine (as we've used with the functions above such as Average) and the number of the quartile. The following values can be used for this second input to the function:

0 = Lowest Value
1 = Quartile 1
2 = Median
3 = Quartile 3
4 = Maximum Value

As you can see, you could have used the Quartile function to calculate the minimum and maximum values and the median, but the functions specifically for those values are simpler to use and understand. To find the first quartile, use the formula as in the screenshot below:



Notice, most importantly, the comma and number 1 after the range of cells to examine. The formula for the third quartile is shown here, and you can see the use of the three as input to the formula to find that quartile.



As with the Average, Lowest, and Highest, we can copy the cells with these formulas to all the other grades to see their values.

Here is how our spreadsheet looks for the Homework grades:



Those statistics should get you started on examining your grades, and will conclude this Tech Tip. If there's a statistic you're interested in that is not covered here, see Tech Tip #2 for ways to look up functions in Excel - it may have it built-in for you.