Spreadsheet tips for grading
Various spreadsheets have some lesser known formulas or functions that teachers can use for grading. It is not necessary to install or buy extra third-party apps or plug-ins for these features, but it does take some understanding of some intermediate-level spreadsheet syntax that many teachers have never been taught in their teacher training programs. I use these myself, and for the more complex ones, at the end of each semester as I calculate grades, I find myself having to look online again for instructions on some of these. So for my sake, and for other teachers, I have created this guide to help you. These should work for any spreadsheet program, but when necessary, I am commenting on subtle syntax differences between major spreadsheets, namely, Excel, Google Sheets, and LibreOffice Calc.
Curving grades
Let's say you give a quiz that a lot of students did poorly on, but a few did okay. Out of fairness, you would like to curve up the grades. You could just add a few points in the spreadsheet, or multiply the raw score by a number like 1.05; but if a few students scored in the upper 90s, you might have scores over 100 - which is fine if you like giving extra credit. Otherwise, you will need to use a conditional formula to put an upper limit on the score. I will use mathematical functions with f(x) for the raw score, with examples of spreadsheet formulas.
f(x) = x+2 -> =B2+2 f(x) = x*1.05 -> = 1.05*B2
You can use a conditional function ("IF") to make sure grades to not exceed 100.
=if(condition, if-true, if-false)
The first statement in the parentheses states the condition, for example, if cell B2 with a score multiplied by 1.05 is less than 100, then the adjusted score is 1.05x; but if it would exceed 100, then simple assign a score of 100.
=if(1.05*B2<100.1,1.05*B2,100)
If you are feeling stingy or grinchy, you can of course adjust grades down by subtracting or by multiplying with an integer below 1.0.
=B2-2 =.98*B2
However, if a few students did well, but many did poorly, then a proportional curve can be done using a square root function. Students receive an increase proportional to how poor their score is. Those with high scores receive little or no increase, those with medium scores receive a greater boost, and those with low scores receive the greatest increase, proportionally. This can be the fairest option for a difficult test or assignment. This is done by taking the square root of the raw score and multiplying by 10.
f(x) = 10√x
This can be done easily in spreadsheets with the POWER function, or if you prefer a slightly more complicated way, the carat symbol (^) to raise a number to 0.5.
=10*(B2^.5) =10*POWER(B2, 1/2)
If this leads to too much of an increase for you, you can do a moderate square root curve, by averaging the square-adjusted score with the raw score, which curves the grades by half as much as the square root adjustment. The curve is still proportionally greater for lower scores, but the amount of increase overall is halved.
f(x) = (10√x) = x / 2 =AVERAGE(B2,(10*POWER(B2,1/2)))
