Introduction

The Grades Management macros are a group of functions that are added to the OpenOffice spreadsheet application to assist with performing many common grade-related calculations. The macros appear under their own "Grades" menu in OpenOffice.

Most of the macros work by taking a column of grade data, performing calculations on each grade value, and putting the results in a new column. The original column remains unchanged. In almost all cases, the new column will contain spreadsheet formulas so that if the original data is modified, the new column will automatically recalculate. For example, consider the simple Round macro, which rounds grades to the nearest whole number. Say we have the grade 67.8 stored in cell B5, and we apply the Round macro to column B and put the results in column C. Instead of containing just the rounded grade value, 68, column C5 will actually contain the spreadsheet formula =ROUND(B5; 0). Therefore, if we change the grade in cell B5 to 68.5, cell C5 will automatically recalculate and display 69. If a particular macro is unable to use spreadsheet formulas for its calculation, this will be noted in the macro description.

Setting up the Spreadsheet

The Grades Management macros assume that the first 3 rows of the spreadsheet have been reserved to hold the following:

Row 1: Column titles
The column title is largely unused by the macros, but it is copied over into new columns by most macros.
Row 2: Perfect score
The perfect score is used by many of the macros and it is highly recommended that you fill it in. A perfect score of 100 is assumed otherwise.
Row 3: Weight
The weight is used by the Final Grade and Term Grade calculations, and can be left blank if you are not using those macros. Weights are expressed as numbers between 0 and 1, and should add up to 1. For example, if a test is worth 15% of the final grade, enter 0.15 as the weight for this column.

Here is an example of a spreadsheet that is ready for use with all the Grades Management macros:

Special Codes

The only special code recognized by the macros is EXCU, which is used to indicate that a student has been excused from the assignment or test. This code is used in a few of the macros, particularly the Excused Averaging macro and the Final Grade and Term Grade calculations.

Recovering From Errors

The operations performed by the Grades Management macros are standard spreadsheet operations that can be reversed with the spreadsheet's Undo feature. For example, if you accidentally overwrite a column of grades with a macro, you can use Undo to restore the original data.

The Macros

In most cases, using a macro will insert a formula that will automatically recalculate in response to any changes to the grade data. If this is not the case for a particular macro, this will be noted in the macro description.

Final Grade Calculation

A final grade is calculated based on the specified columns, using the perfect scores and weights for each column. If a perfect score is not specified for a column, it is assumed to be 100. The weights of all columns must be specified and add up to 1. The final grade is expressed as a percentage.

There are several options for the calculation:

  • Standard: the normal calculation
  • If any < 50%, FAIL: if any component of the grade is less than 50%, the final grade is reported as FAIL
  • If EXCU, drop weight: grades with the value EXCU are ignored by the calculation
  • If EXCU, shift weight: grades with the value EXCU have the weight of their column shifted (added) to another column. A special formula is entered to determine the shift. For example, to shift the weight of column A onto column B, the formula would be A>B. Multiple shifts can be entered if they are separated by commas. A single column can receive two shifts at most.

The images below show the results of using the Final Grade macro. The "If EXCU, drop weight" option was used here.

Term Grade Calculation

A term grade is calculated based on the specified columns, using the perfect scores and weights for each column. The weights of all columns must be specified and can add up to less than 1. The term grade is expressed as a percentage.

There are several options for the calculation:

  • Standard: the normal calculation
  • If any < 50%, FAIL: if any component of the grade is less than 50%, the final grade is reported as FAIL
  • If EXCU, drop weight: grades with the value EXCU are ignored by the calculation
  • If EXCU, shift weight: grades with the value EXCU have the weight of their column shifted (added) to another column. A special formula is entered to determine the shift. For example, to shift the weight of column A onto column B, the formula would be A>B. Multiple shifts can be entered if they are separated by commas. A single column can receive two shifts at most.

The images below show the results of using the Term Grade macro. The "Standard" option was used here.

Round Grades

Grades are rounded to the nearest whole number. For example, 48.5 is rounded to 49.

Round and Bump 9s

Grades are rounded to the near whole number, and if that number ends in 9, bumped up to the nearest multiple of 10. For example, 48.5 is rounded to 49 and then bumped to 50.

Rankings

Students are ranked based on their grade. The highest grade is ranked 1.

Letter Grades

Students are assigned a letter based on their grade. There are three different schemes available for determining the letter. Letter assignment is based on the exact values in the column, which are assumed to be out of 100.

The images below show the results of using the Letter Grades macro. The default scheme for letter assignment was used.

Pass/Fail Grades

Students are assigned either PASS or FAIL by comparing their grade to the specified threshold. Students may also be assigned SAT rather than PASS, or HON if their grade exceeds a second threshold.

The images below show the results of using the Pass/Fail macro. The pass threshold was set at 50%, with grades above 80% receiving the designation HON.

Percentages

Grades are converted to a percentage based on the perfect score for the column.

Percentiles

Grades are converted to percentiles based on one of two methods. The default method uses the spreadsheet's built-in PERCENTRANK formula which produces values between 0 and 1. The alternate method uses the following formula:

(cf + 0.5(f))/n * 100%

cf
The cumulative frequency of all scores lower than the score of interest
f
The frequency of scores in the interval of interest
n
The number of values

The images below show the results of the Percentile macro. It was run once in column F with the default method, and again in column G with the alternate method.

Best Sum

The top x grades from the input columns are added together. The perfect scores for each column are assumed to be the same, so the top grades are determined purely by a comparison of their magnitude.

The images below show the results of the Best Sum macro. It was configured to select the best 3 grades.

Best Average

The grades from the input columns are converted to averages based on the perfect scores for each column. The top x averages are then themselves averaged and used to generate a final score based on the perfect score indicated for the output column. If no perfect score has been set for the output column, a score of 100 will be assumed.

NOTE: This macro will not automatically update based on changes to input data, and must be run again each time a grade change is made.

The images below show the results of the Best Average macro. It was configured to select the best 3, and the result was mapped to a perfect score of 30.

Excused Averaging

Calculates an average of the averages of the grades in the input columns. When EXCU is encountered, the column is removed from the calculation for that student. The final result is mapped to the perfect score of the output column, or to 100 if no perfect score is specified.

The images below show the results of the Excused Averaging macro.

If-Then-Else

Assigns one of two possible values based on the specified test. For example, one use would be to assign a bonus participation mark (5%) to any students who missed fewer than 3 lectures. If the number of missed lectures were in column A, the statement would look like this:

IF A < 3 THEN 5 ELSE 0

Standardize

Grades are standardized to the given mean and standard deviation using the Z-score for each grade.

The image below shows the results of the Standardize macro. The mean was set to 65 and the standard deviation to 10.

Normalize

Grades are normalized to the given mean and standard deviation by mapping the percentile rank for each grade onto the normal distribution.

The image below shows the results of the Normalize macro. The mean was set to 65 and the standard deviation to 10.

Pearson Correlation Coefficient

Calculates the Pearson Correlation Coefficient, known as r, of two input columns, which is used to determine the degree of dependence between two variables.

The image below shows the results of the Pearson Correlation Coefficient macro.

t-Test for Correlated Means

Performs a two-tailed t-test for paired samples on two input columns. The perfect scores for the input columns must be the same or the results will be meaningless. The results are presented as p, t, and df values.

The image below shows the results of the t-Test macro.