Weighted Scores

This is the hardest part of the whole thing, but it’s not really that bad.  It’s time to use the formula for weighting the scores.  The formula looks like this:

=SUM(project score x project weight, test score x test weight)/SUM(project weight, test weight)

I could just use the actual number for the weight of each grade, without referring to the cell.  However, I do refer to the cells so I can see the grade weights without having to look at the formulas.  Also, if I need to change a weight, I can do so easily.

The formula does have a lot of variables, but I’m only working with a few cells here. I can also use my mouse to click on the cell I need so I don’t have to hassle with the cell names.  So, when all the numbers are applied, I end up with:

=SUM(E19*F19,I19*J19)/SUM(F19/J19)

And I’m done.  I have a final grade for the term.  Once I make sure the calculation is in proper format, it’s time to add the grade to the master page.

I go back to the main report, find the cell for the right subject and the right term and enter this formula:

=SUM(page!cell number)

So, in this case, I get:

=SUM(Art!C24)

Now, any updates I make to the grade will automatically change the final grade on the main page, keeping the report card perfectly up-to-date.  At the end of the term, I go back and add a letter grade manually.  I’ve never bothered to see if there is a formula for assigning a letter grade.

If you’re still a little confused, or just want to play with a gradebook, I’ve included a sample file with a main page and two subjects.  It’s available here as an Excel file.  Don’t worry, you can snag an Excel viewer for free if you don’t have Excel.

Still want more?  If you want to know how to do a three-category weighted grade, you can go to the next page to see it in action.  It’s easy, I promise.

Optional Step – Three Category Grades >