Filtering Responses and Calculating Grades

Now that we've got the data, we have to work with it...

So we have our spreadsheet set up, linked with the form, and our separate sheets are all created. Now we need to take the data from the "entry" sheet, and use the data to calculate grades.

There are only a few commands that we need to use to accomplish this. There is a database of help that covers each command, so if after reading this, you're still unsure, check it out here. The first one I'll talk about is the FILTER command.

Filter

Filter is a particularly useful command when you want to compare cells to a criteria. In our application, FILTER will compare each student's answer with the first set of answers received (which, as you remember, is the answer key you set up before you published the results). The really wonderful part of this command is that you can apply it to an entire column (called an array in geek-speak), so as each entry comes in, you don't have to worry about running out of cells with proper formatting.

The syntax for the FILTER command is a little confusing if you're new to spreadsheets. To tell the sheet that it's a command, and not an entry, you have to start by typing =FILTER in the cell. The equals symbol will tell the sheet to apply the command, rather than just enter the text.

When using the filter command, it follows the following pattern: =FILTER(*column you're working with*,*column to compare* *operator* *cell for comparison*). 

When giving an array (that is, when you specify an entire column), you specify the cell to begin with, and the cell to end with. If there's no cell to end, you just use the letter of the column. For example, if I want to filter column A starting with cell 3, I would type A3:A (which will start with the 3rd row, and continue on from there). In my case, the command on the "results" sheet was the following: =FILTER('entry'!E3:E,'entry'!E3:E<>'entry'!E2) - this compares the submissions against the first submission (the answer key), and filters out any matches. Thus, only incorrect responses will show.

The next command that we need is the IF command. This compares the contents of a cell against a condition, and executes a function based on the results. If you've ever seen any programming code, you'll recognize this as.one of the basic functions in most programming languages. If you've never seen code, then don't worry (there, I've just given you and example of how the command works!). I used a separate sheet to calculate results. When I was researching how other people have done this, they used a filter command and an if command nested together, but for some reason I couldn't get that to work properly. Rather than bang my head against a wall over it, I just used another sheet as a work-around. The grade sheet calculates the grades, using the weighting given for each question in the answer key sheet.

The IF command follows this pattern: =IF(condition, result 1, result 2). The command checks the condition, which has to be either true or false. If the condition is true, it returns result 1, otherwise it returns result 2. Because we have filtered out the correct responses, we will check each cell on the results sheet to see if it is empty. Empty cells will be return the weighted value, and non-empty cells will return zero. Thus, the command looks like this: =IF(results!E3="",key!B3,0).

Finally, we use the SUM command to find the sum of the results, the sum of the weights, and the quotient of these two will be the user's grade.