Faculty and other instructors at Boston University often have questions about how to calculate grades with Excel: how to calculate averages, drop low scores, use weighted scores and how to assign letter grades. This web page explains some features in Excel that can help you to perform these tasks.
The function =AVERAGE() can be used to calculate a simple average. In the parentheses, you simply enter the range of cells you want to average, in the form (first:last).
This is illustrated in cell G2 below.
Here’s the result:
Using SUM() instead
An alternative way of calculating the average is with the function =SUM(B2:F2)/5. This seems like more work than just using the AVERAGE function — and it is! But if you get into more complicated calculations, like the ones below, you will find this method more flexible than using =AVERAGE().
Dropping the lowest score
In order to find the lowest score, you can use the MIN() function.
=MIN(B2:F2) will give you the answer 76.
To average the grades excluding the lowest score, use this formula
=(SUM(B2:F2)-MIN(B2:F2))/4. Note that once we drop the low score, we are only dealing with 4 numbers, so that is whay we have to divide by 4, rather than 5.
The disadvantage of the simple average is that it assumes that every score has the same weight: that Quiz 2 is as important to the final grade as the Final Exam. If this is not the case, you will want to use a weighted average instead.
There are two different ways of calculating weighted averages: by entering the weights by hand as you create the formula, or by entering the weights on the spreadsheet and using the =SUMPRODUCT forumla. We’ll look at both methods.
Weighted averages by hand
Let’s look at Jane’s scores, again.
We used the =AVERAGE() function to determine her grade before. Another way we could calculate a simple average for Jane would be with the formula =(B2+C2+D2+E2+F2)/5 — in other words, we add the scores, then divide by the number of scores we are adding.
To determine a weighted score for her, we simply modify this formula to reflect the importance of each score. So if the project (E2) is worth twice as much as a quiz, and the final project is worth 3 times as much as a quiz, we could use this formula. =(B2+C2+D2+2*E2+3*F2)/8. Why do we divide by 8? It’s slightly tricky: we are now dividing by 8, because in a way we are dealing with 8 items — we count the B2, C2, and D2 values once each, the E2 value twice, and the F2 values 3 times (so 1+1+1+2+3=8).
Weighted averages with SUMPRODUCT
You can also use the SUMPRODUCT function to calculate weighted grades, if you prefer. To do this, you need to have a row in your spreadsheet that shows the weight of each grade. For example, in the example described above, quizzes would each be 1/8 or 0.125 of the grade, the project would be twice that or 1/4 or 0.25, and the final exam would be three times a quiz, 3/8, or 0.375. You can insert a row in your spreadsheet to hold these values.
Once you have that set up as shown below, the formula is basically =SUMPRODUCT(range of grades, weight of grades). In the example below, it is =SUMPRODUCT(B2:F2, B3:F3).
NOTE: If you plan to copy this formula to other cells, you should put $ marks in the formula around the names of the cells where the weighting information is stored — so the formula would instead look like this: =SUMPRODUCT(B2:F2, $B$3:$F$3). The dollar signs prevent Excel from trying to “adjust” the range of cells that contain the grade weighting, and instead keep it looking in the exact cells you set up for this information (in this example, B3 to F3). The dollar signs are used to indicate an “absolute” reference — you can learn more about this by using Excel’s Help function, if you wish.
There is more information about SUMPRODUCT in the resources listed at the end of this document.
Assigning letter grades
To assign letter grades in Excel, you use the =LOOKUP() function to tell Excel which letter grade you want to assign to each score.
To use this, take an out-of the way part of your spreadsheet, away from the student names and data and set up two columns. The column on the left shows each possible letter grade and to the right, the minimum score required to earn that grade. In this example, someone who scores 79 would get a C+ and someone who scores 80 would get a B-.
Note: these scores came off the top of my head and so do not reflect the actual grade cut-offs for your class or department. Use your own numbers.
Now, in your spreadsheet you will be able to use the LOOKUP() function to check a student score against this list and assign the appropriate grade.
And here is the result:
Calculating a student’s rank within the class
The functions RANK and PERCENTRANK can be used to calculate each student’s rank or percentile rank within the class. If Jane Smith’s final score is in cell G2 and the final scores for the full class are in cells G2 through G22, Jane’s rank in the class is given by the formula
and her percentile rank is given by
I hope this page has answered some of your questions about managing grades in Microsoft Excel. There are some other resources available on the Web to help you with grade management; here are a couple I’ve found useful:
- Penn State: Manage your Grades Electronically with Excel
- UMich: Getting Started with Excel 2003 (PDF) (includes info on SUMPRODUCT)