Spreadsheet Tool- The Grade Calculator

Calculating a Score and a Grade

If you need a basic grade calculator that handles both weighed and non-weighed work and handles both points and percentages - and that is all, then this might be the tool for you. At its most basic function this tool will return a percentage score from points and that is very useful, so useful that I have included a similar tool in the V Planner.

The Grade Calculator discussed on this page is a Microsoft Excel file, a spreadsheet, and you must have a spreadsheet program on your computer to open and use the file.

PLEASE Note- This tool is not a Grade Book; it does not store a record of the grades - it is merely a set of calculators that return a grade that you can place in a grade book.

The Purpose of the Grade Calculator

The calculator will return a score from a test, a quiz, a project, any one instance of graded school work that contains from one to seven weighed parts.

What You Do

After grading work, enter the numbers into a grade calculator. The calculator will return a score that you can enter into your grade book.

The GRADE - In addition to the score, the calculator will return a grade. I added the grade function to the file, not because I think you NEED it, but because I wanted to give you the option of seeing a grade and possibly using it. Typically the score, not the grade, is entered into the grade book, or at least they are in my grade books.

IF you want to use the grade that is returned, be sure to visit the Grade Scale worksheet to view and edit, if necessary, the grade scales.

My Terminology: Grade vs Score

This was copied from my V Planner V3 GS-QP V Planner Help File

In order to talk about grades, I am forcing myself (and trying to remember) to use the definitions listed below regarding grades. It is possible that I am making these terms up and that someone else might use them differently, but when you speak with me or read the help pages for V3 (and this page), I will refer to these two commonly used grade-related terms as such --

• Scores - the number, such as 80%
• Grades - the letter, such as a "B"

The Files

The visible content in the two files below is the same. Choose a file based on the newest version that your software supports - either XLS (Office 97-2003) or XLSX (Office 2007 and newer).

How to Use the Grade Calculators

This part assumes that you have downloaded a file and you have opened the file in your spreadsheet software.

Points and Percentage

These two worksheets are almost the same and using the worksheets is almost the same. First I will go over the shared instructions and then I will go over the worksheet specific instructions.

The Simple Score

Simple Score - Not Weighed
1. It does not matter if you are at Points or Percentage, use either one for the simple score.
2. In the Yellow Cell A8 enter the number of problems
3. In the Green Cell A9 enter the number of missed problems.
4. The score is displayed in Cell A12

Work that Contains Parts with Different Values, "Weighed"

If the test or work has parts with different values, then use either the Points worksheet or the Percentage worksheet. Either one of those two worksheets can handle up to 7 values.

Which one to use should be clear - if the values are percentages, use the Percentage worksheet, otherwise, use the Points worksheet.

Basic Instructions
1. In the Blue row (ROW 7) starting at cell E7, enter the weight values. You can enter up to 7 different values. Unneeded columns may be left empty.
2. In the Yellow row (ROW 8) enter the number of problems for each weight value.
3. In the Green row (ROW 9) enter the number of missed problems for each weight value.
4. The score is displayed in ROW 12
5. Read about the Grade (the letter) below.

The Percentage Worksheet

It's different from the old Grade Calculators.

1. The values entered in the blue row on the Percentage worksheet do not have to equal 100%.
2. A counter that adds the percentages as they are added is displayed in Cell C7.
3. Two rows below the green row shows you the individual section scores.
4. Cell J4 - You have a choice of TWO calculations methods, read more below.

When entering values in the Percentage worksheet, do not use the % key. The cells are formatted to enter that for you.

What if The Weight Values Do Not Equal 100%?

You're in trouble. I'm just kidding. I added two calculating options in Cell J4 on the Percentage worksheet and they are Prorated and NOT Prorated.

• Prorated - Proportional Distribution - When you choose Prorated, the percentages that are entered into the blue row will be altered so that the sum of the percentages equals 100%. What this means is if the percentages are less or more than 100%, the score will be calculated as if they equaled 100%. Additionally, and this should be important to you - The values are proportionally altered - that means the value retains its weight relative to the other values that are entered.

  The prorated setting can be used all the time. The only time that one might not want to have the setting is if you WANT the calculation to be based on the actual sum of the percentages EVEN IF they do not equal 100%.
  I added a demonstration of proration on the About Worksheet.

• NOT Prorated - Not Prorated simply means that the score will be calculated based on the actual percentage values in the blue row. This is excellent, this is how it's done, but usually only as long as the percentage values equal 100%. However, if the values are less or more than 100%, the score will not be the typical "out of 100%" type of score. This might be exactly what you want and that is why I gave you two different options.

The Points Worksheet

There is nothing different at the Points worksheet.

The Grade Scale Worksheet

Not Using the Letter Grade - Using the grade scale is completely optional! If you do not need to see a letter grade for the score, then click in cell G12 (this is the cell with the drop-down list) and press the backspace key. That will make the letter grade go away.

Set Up The Grade Scale - If you would like to see a letter grade for the score, go to the grade scale worksheet and view the grade scales.

First Ask Yourself - "Which grade scale matches my grade scale?"

1. If one of the grade scales matches, take note of which one it is (the names are in Column P) and select that grade scale at the Points and Percentage worksheets in Cell G12.
2. If none of the grade scales matches your grade scale, choose one of the grade scales and edit it. There are two ways to edit the grade scales and those two ways are explained on the worksheet.

Donna Young, January 7, 2014

Hello Visitor!

I am currently working on this website to add to its ginormousness. Thank you for visiting, and please subscribe yearly to access my many printable files! Donna Young
May 13, 2021