Difference between revisions of "Spreadsheet tips for grading"

From English Wiki
Jump to navigation Jump to search
m
m
Line 209: Line 209:
 
(Forthcoming)  
 
(Forthcoming)  
  
 +
 +
==Text & number format problems==
 +
Sometimes you may find that a formula is not working properly, e.g., giving erroneous results, or displaying an error message. Often this is because the range of cells in question is in text format, when the function requires numerical format. This can be solved by highlighting the cells, pressing CTL-1, and changing the format from text or general to numeric.
 +
 +
You may find extraneous lead apostrophes in cells, for example, '10 instead of 10. This is particularly a problem in LibreOffice, either after converting cells from text to numeric format, or pasting in data from another program, or importing data from another file format like CSV. LibreOffice has a tool to correct this. Highlight the cells, and on the menu bar, find Data > Columns to text, and click OK to apply it to the cells. Problem solved.
  
  
 
[[Category:Assessment]] [[Category:Pedagogy]]
 
[[Category:Assessment]] [[Category:Pedagogy]]

Revision as of 04:18, 22 December 2016

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, subtle syntax differences between major spreadsheets, namely, Excel, Google Sheets, and LibreOffice Calc are mentioned.


1 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  (additive)
f(x) = x*1.05 -> = 1.05*B2  (multiplicative) 

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. You can use the less-than symbol (<), or "<=" for the less-than or equal-to symbol in spreadsheets.

=IF(1.05*B2<=100,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 (subtractive)
=.98*B2 (multiplicative) 

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)))

The following table compares these methods for raw scores (x).

raw score simple curve-up adjusted simple curve-up curve-up root function mild curve root function curve down
x 1.05x 1.05x if x≤100 f(x) = 10√x f(x) = (10√x) = x / 2 .98x
B2, B3, ... =1.05*B2 =IF(1.05*B2<=100,1.05*B2,100) =10*POWER(B2, 1/2) =AVERAGE(B2,(10*POWER(B2,1/2))) =.98*B2
100 105.0 100.0 100.0 100.0 98.0
99 104.0 100.0 99.5 99.2 97.0
97 101.9 100.0 98.5 97.7 95.1
95 99.8 99.8 97.5 96.2 93.1
92 96.6 96.6 95.9 94.0 90.2
90 94.5 94.5 94.9 92.4 88.2
89 93.5 93.5 94.3 91.7 87.2
88 92.4 92.4 93.8 90.9 86.2
85 89.3 89.3 92.2 88.6 83.3
81 85.1 85.1 90.0 85.5 79.4
78 81.9 81.9 88.3 83.2 76.4
75 78.8 78.8 86.6 80.8 73.5
70 73.5 73.5 83.7 76.8 68.6
64 67.2 67.2 80.0 72.0 62.7
0 0 0 0 0 0

2 Dropping the lowest score

For a set of homework assignments, you want to drop the lowest score for calculating students' homework averages. The easiest way to drop one low score is to use the MIN function to find the lowest number in a range and remove it from the range of numbers to be summed and average. For these score-drop functions, the SUM/n function (n=number of scores) works better than the AVERAGE function. The MIN function requires a range, that is, the cells with one student's scores.

=MIN(range)
=MIN(A2:L2) 

This finds the lowest score, and this function can be plugged into the SUM function to remove the lowest score and average the remaining scores.

=(SUM(A2:L2)-MIN(A2:L2))/10 

Dropping more than one score requires the SMALL function instead, as MIN can only identify the very lowest score in a range. The SMALL function requires a range of cells, and the xth smallest number that you want, e.g., the very smallest (1), the second smallest (2), the third smallest (3), and so on.

=SMALL(range, xth-smallest)
=SMALL(A2:L2,1) 
=SMALL(A2:L2,2)

These can be plugged into the summation formula, and you can use either MIN or SMALL(range,1) to identify the lowest score. Either of these formulas will average a students' scores minus the lowest two.

=SUM((A2:L2)-MIN(A2:L2,1)-SMALL(A2:L2,2)/10
=SUM((A2:L2)-SMALL(A2:L2,1)-SMALL(A2:L2,2))/10


3 Counting instances

The COUNTIF function can count the number of times that an item occurs in a range of cells. For example, I track students' absences and tardies in a spreadsheet. At the end of the semester, I tally them to deduct points from their participation grades. The COUNTIF function requires a range of cells to evaluate, and the target item that you are looking for. If it is in text format, enclose it in quotation marks, and if it is a range of numbers, make sure that the cell text format is text or numeric, as this might affect how these and other functions work (highlight the cells and press CTL-1 to check the cell format).

=COUNTIF(range, "target")

For example, I want to count the number of tardies for student A, marked "l" for late, and the number of unexcused absences, marked "x" in my spreadsheet.

=COUNTIF(A2:AA2,"l")
=COUNTIF(A2:AA2,"x")


4 Conditional function

The conditional IF function, as seen above, evaluates a condition on a target cell or cell range, and enters data in the current cell according to whether the condition holds true for the cell(s).

=IF(condition, if-true, if-false) 

The condition itself can be a simple equation with an equality (=), inequality (< or >), greater than or equals (=>) or less than or equals (<=). If an element in the equation is a text string, quotation marks are used. For example, if a neighboring cell contains an "x", a 0 is entered in the current cell, but if not, then a 1 is entered.

=IF(A2="x",0,1)

Other examples:

=IF(A2<100,"yes","no")  
=IF(A2<=100,"yes","no")

Equations can also be used for the if-true or if-false statements. For example, this formula evaluates cell A2, takes the score from cell B2, adds or deducts one point from B2 depending on the value of A2, and enters it into the current cell.

=IF(B2="x",A2-1,A2+1) 

Conditional statements can be plugged into larger formulas. For example, I assign a basic participation grade for in-class participation, but I deduct points for more than two absences, that is, if the number of absences (reported in cell X2, from the previous COUNTIF function) exceeds 2.

=Z2-(IF(X2>2,(3*(X2-2)),0))  

This can be broken down like so:

condition: IF(X2>2)
then: Z2 - (3*(X-2)
else: Z2 - 0
altogether: =Basic participation in Z2 - [IF X2 > 2, then (3-point-penalty)*(#absences -2)] 

To make things more complicated, the COUNTIF function could be embedded into the above conditional, which evaluates the attendance list (cells 2 to T2 for this student) and deducts points all in one complicated function. The more complicated a formula becomes, the easier it is to make syntax errors, e.g., with opening and closing parentheses.

=IF((COUNTIF(C2:T2,"x")>2),Z2-(3*(X2-2)),Z2) 


5 Combining data across tabs or sheets

VLOOKUP function (Forthcoming)


6 Text & number format problems

Sometimes you may find that a formula is not working properly, e.g., giving erroneous results, or displaying an error message. Often this is because the range of cells in question is in text format, when the function requires numerical format. This can be solved by highlighting the cells, pressing CTL-1, and changing the format from text or general to numeric.

You may find extraneous lead apostrophes in cells, for example, '10 instead of 10. This is particularly a problem in LibreOffice, either after converting cells from text to numeric format, or pasting in data from another program, or importing data from another file format like CSV. LibreOffice has a tool to correct this. Highlight the cells, and on the menu bar, find Data > Columns to text, and click OK to apply it to the cells. Problem solved.