"What are we doing to help students achieve?"
Every chemistry teacher I know wants to do hands on and minds on labs. The struggle sometimes is with the number of students and the time required for grading papers. Suppose you have a hundred students and you want to do a lab, perhaps a stoichiometry lab, with quantitative data. If each student does the lab all of the sudden it becomes a grading headache. I always feel I have the responsibility to check for significant numbers, calculations and labels. This could become a ton of squeeze but not much juice. There is always the problem of one student copying off everyone else when I have students work in groups. A spreadsheet and a spreadsheet grader can lighten the load and still keep students responsible. It will be helpful if you DOWNLOAD the google spreadsheet that I have created for a lab that I use. There are three tabs at the bottom of the spreadsheet that will be the focus of this blog. The tabs are “Student Data and Calcs”, “ Teacher Grader” and “Change Color based on Answer”.
Student Data And Calculations
The first step is to decide what data and calculated answers I want from students. I always make sure to have the data tables first and then the calculated answers second in the spreadsheet. I make it really clear to students that they are only supposed to put in the numbers and not the numbers with the labels. It is easier to have spreadsheets read just numbers.
The purpose of this blog is to examine spreadsheets more than the lab activity itself but I need to set the context of this example lab and the spreadsheet. Although this idea could be used for any number of quantitative data type labs I am going to use it for a general stoichiometry lab here. In the lab the students can either use iron filings or a clean iron nail and react it with copper (II) sulfate or copper (II) chloride. They are presented with one of two reactions. I have chosen the reaction of iron filings with copper (II) sulfate.
A. Fe(s) + CuSO4 (aq) à FeSO4 (aq) + Cu(s)
B. 2 Fe(s) + 3CuSO4 (aq) à Fe2(SO4)3 (aq) + 3 Cu(s)
One nice aspect of this lab is that there is a Flinn Lab - Iron Filings: A Mole Lab that Actually Works available as a free download with the full write up. If you happen to have an Atomsmith membership like I do, the reaction is also modeled there.
Here is the essential part of this lab whether it is done with students using copper (II) sulfate or copper (II) chloride.
- Mass the solid iron before the reaction.
- Mass the solid copper collected.
The students then convert the solid iron and solid copper into moles. If they get a one to one mole ratio then reaction “A” is the reaction. If they get a two to three iron to copper mole to mole ratio then most likely it is reaction “B”. Here is a typical spreadsheet data table. I use iron nails and copper (II) sulfate. The lab is attached to this blog in the Supporting Information below.
The same spreadsheet with typical student data can be see below.
Teacher Grader. There are some key points at this stage. Students send me the electronic copy. I make it clear that everyone has to also submit a hard copy with the detailed calculations. I can electronically copy the above table into my spreadsheet which is the “grader” and then hit “print”. Below is what that would look like.
The key to creating a spreadsheet that does the grading for you is NOT that you need to know how to code. You simply need to know someone who knows how to code and understand the code enough to be able to copy, paste and tweak it a little bit. So let’s take a look and break this down a bit.
The first step is to calculate what the correct answers are based on the data the student inputs. Look at cells C6 through C12 that are in red. The purpose of these cells are to create a space in which the correct calculated answers are present based on student data. If you click on cell C7 “The mass of iron reacted (g)” here is the code that appears in tool bar.
“=B2-B3”
This is essentially saying that anything in cell C7 should equal the values of cells B2, Mass of iron nail (g) - B3, Mass of iron nail after the reaction (g). C8 through C11 are just typical calculations or “functions” in spreadsheet lanquage. Any calculation or function you want in a cell always starts with an “=” sign and then the calculation. Instead of the numbers, you simply put in the “cells” where the numbers can be found. As an example, C8 is “Moles of Iron used in reaction”. This is calculated by the mass of the iron divided by the molecular weight of the iron. So, to tell the spreadsheet to do this in cell C8, here is the equation.
“=C7/55.84”
This equation is saying, “Take whatever is in cell C7 which is the mass of iron and divide it by 55.84 the molecular weight of iron and give me the answer”. Take a minute and look at some of the equations for C7 through C11 and match the cells, equations and answers. The code in C12 is different. This involves an “If, then” statement.
=IF(B11>=0.84,"A","B")
At first glance, this looks confusing until we unpack the different parts. The equal sign (=) indicates that there is a function, equation or command that we are telling the spreadsheet to do and then put the answer in the cell C12. This is saying, “If cell B11, which is the student cell of the ratio of iron to copper, is greater than .84 then (the first comma means “then”) put in the letter “A”. If not (this is the second comma) then put in the letter “B”. Here we are trying to tell the computer that if a student has equation “A” then it must be a one to one mole ratio of iron to copper or a ratio that is close to one to one.
At this point we have data, student calculations and spreadsheet calculations based on student data. Now the level of complexity increases. The goal now is to basically tell different cells to compare the correctly calculated answer in red to the student answer, allow for a percentage of error, and assign points if the answers are correct. First, there has to be a place for the instructor to put in the points for each question and amount of error allowed. This is the point of the columns F-H. Please see below.
This part of the spreadsheet is meant for the instructor to set points and allowance for error for each of the calculated answers. This table is important because it provides a place for us to tell the spreadsheet to get information and assign points based on student answers. As an example, the second row above says that in Row 7, which is the mass of iron calculated, the teacher will allow a ten percent variance and if the student calculated it correctly or within that variance they get 2 points. Rows 8-11 are read in a similar fashion for the answers they refer to and row 12 is “not applicable” because here the student in question 12 choses between one of two answers and it is either right or wrong. The “Default Points” in cell I3 allows a teacher to give a student points just for doing the lab. Let’s say that the default points are set to “10”. This means that the student can get every calculated answer wrong and still get 10 points just for doing the assignment. Now that we have our “points bank” in columns F-I, let’s figure out how to use them.
Here is the last part of the spreadsheet. The “Within Range?” column is a place where the spreadsheet is going to check the student's answer, compare it to the calculated answer from the student data in red, decide if it is within the acceptable range set by the instructor and if it is, assign points.
Let’s start by looking at the coding in cells D7 and E7 as an example. Once you understand these two cells you can see that the coding in D and E rows 8 through 12 are similar. Cell D7 has the following code that is going to compare the student calculated answer of mass of iron reacted in B7 and the calculated answer by the instructor in red in C7. We want to give students points for having their calculated answer match the instructors calculated answer. Here is the code:
=AND((B7<=(C7+(C7*G2))),(B7>=(C7-(C7*G2))))
Two conditions must be met for the statement to be considered valid and true. The first part starts with the “AND” command. This will provide two statements that must be true. First, the student answer for the mass of iron reacted (cell B7) must be less than or equal to (<=) the instructors calculated answer in red (C7) and the students are allowed to be a little over (+(C7*G2)). Recall that G2 is the cell previously that allows you to set the percent that the student answer can be over or under by. This part of the code says that it can by the actual answer or 10% higher. If you want the variance higher or lower you can change G2. The second part of this says that the student answer (B7) can be greater than or equal to the calculated answer by the instructor minus 10% (>=(C7-(C7*G2))). If comparing the student calculated answer and the instructor answer in red meets either of these conditions then the statement is “True”. Now we just need to provide points for this “True” answer.
The coding in cell E7 is a relatively simple “If Then” statement that provides points for the student calculating the correct answer within the range set by the instructor. This code is similar to the other point codes in cells E8-E12.
=IF(D7=TRUE,H2,0)
The interpretation of this code is as follows. If cell D7, which represents the comparison of the student and instructor answers within a proper range, are “True”, then take the previously determined points set in cell H2 and place them in cell D7. If it is false, then the students get 0 points. And that is it. There is only one cell left with coding. That is the “Total Score” cell. Here is coding.
=CONCATENATE((SUM(E7:E12)+I2),"/",(SUM(H2:H7)+I2))
The term “concatenate” means that the program will link items together but not calculate them. The first item is the sum of the student points in cells E7 through E12 and the default points earned just for doing the lab (sum(E7:E12)+I2). The code “/” means that this sign will be in the cell as is. The third part of the code is the sum of the points possible and the default points (sum(H2:H7)+I2).
At this point you probably are saying to yourself, “This is a lot of work but is it worth it?”. Yes it is. Students come to class and submit their student spreadsheet. I cut, copy and paste it into the student grader. Then I hit “Print”. It takes literally seconds. I pass back the graded copy with the following instructions. “Find your mistakes if any and tell me how you would correct or fix it.” I will always have students who talk about how unfairly I grade. I explain to them that the spreadsheet simply takes their data, calculates what the answer should be and compares. It is either right or wrong. Another aspect about this that is extremely important is that students start to see how mistakes compound. In the above example it is common that a student will tell me that I did something wrong because they worked so hard and only received 4 out of 14 points. If they were to look closely they entered in the wrong number for “Mass of dry copper produced”. They probably had the correct answer but moved the decimal place one place to the left by accident when entering in their answer. This, in turn, caused a chain reaction and all of the following answers are incorrect. It is important to note that students always must turn in their written work. The spreadsheet portion is a great way for the instructor to tell if they are on the right path.
Some teachers have also used other coding options on the student spreadsheet. It is possible for a code to be placed in one of the cells in which a student will place their calculated answer. The code will change the cell to a certain color if the students are off by a certain percentage. The spreadsheet can be protected to hide the coding.
This is a sample of how this could work for a student spreadsheet. You can check out the third tab in the sample spreadsheet that says “Change Color with answer”. Imagine a student collects data on the length and width of an item. The calculated number in this case is 5 but they put in 4.4. There is a code in cell C7 that says if the number entered in B8 is 90% of the sum of the length and the width (SUM(B2,B3) on the low or high end then it is TRUE. If not, then it is FALSE. The second piece of coding is in B8. The conditional formatting rule states that if B8 is false, then color the cell red. The instructor does this and then can hide and lock the coding. It will not necessarily tell the student the correct answer, but will indicate when they are off by a certain amount. This would be nice for common mistakes like putting the decimal in the wrong place or when they include the weighing paper in the mass when they should not.
These sheets are a bit labor intensive on the front end but I have found they are worth it. Students and the instructor can get accurate, quick and impartial feedback. It does take a bit of work to set up the grader but once you do it you have it in place for future use. All other graders tend to be a version of the first one. It has also been a great benefit to students.
I would like to thank Greg Presnall and Caiden Riggers for help coding questions I had.
