Google Sheets data: how to input, how to extract data.
April 16, 2021 11:33 PM   Subscribe

How can I set up a Google Sheet in order to extract a certain data set? There are many more details inside...

Apologies for the vague title, but I'm not sure how best to word it, and I certainly was unsuccessful in Googling it.

I'm a teacher attempting a research project. I want to give my students a column of vocabulary words in Google Sheets (which can be converted to Excel if needed). There are two tables, side by side, with identical info--one for "before" a learning activity and one for "after." For each word in the column, a row of cells asks students to grade a word along a scale, 1-6, to check their understanding of it. My goal is to see if students didn't know a word well, but through the activity, improved their understanding of it.

I made a copy you can see here.

I want to know how can I have Sheets (or Excel) do the hard work of correlating those two tables? I'm thinking of having students enter in a 1 for "yes" and blank for "no." Is there a formula that Sheets has that can compare the two tables and output some useable charts/graphs? Should the input be something else?

One more thing: the Sheet is just one week's worth of vocab; I'll have probably a dozen of these for each student (around 30) by the end of the term. Which is a LOT, so you can see why I want to automate this as much as I can.
posted by zardoz to Education (11 answers total) 2 users marked this as a favorite
Two thoughts: students enter answers into google forms? It would force consistent answers and still give you spreadsheets.

I think a pivot table might also help?
posted by aniola at 11:51 PM on April 16, 2021 [1 favorite]

Response by poster: Yes, students would enter the answers themselves. I'm doing it through Google Classroom, so I will be able to access all these Sheets.

I have heard of a pivot table. But I'll look into that, thanks.
posted by zardoz at 12:02 AM on April 17, 2021

Best answer: I agree that using a form with radio sets to input would be best so that students can't accidentally put more than one "1" in a row. But I don't see how a pivot table would be useful. I suggest that you assign a value to each of the knowledge levels (the column headings), eg, 0 being the least familiar, 5 being the most confident, and then a line graph with 2 axes (before and after) should give you a good idea at a glance as to the improvement in familiarity. This would be easy to set up. Feel free to memail me if you need any help.
posted by mezzanayne at 12:18 AM on April 17, 2021 [1 favorite]

Is there a formula that Sheets has that can compare the two tables and output some useable charts/graphs?

First thing would be to convert your data entry cells to the 1 to 6 score they're supposed to represent. The approach I'd use for this would be to take the nonblank cell with the lowest score to be definitive and ignore everything else. So to convert the six cells at B4:G4 to a score from 1 to 6 you could use something like
=match(true, arrayformula(trim(B4:G4)>""), 0)
This is robust against students using things other than "1" as cell markers, or blanks being used to erase unwanted markers instead of deleting them.

Once you have all your scores as numbers, you can subtract befores from afters and chart the results.

Doing all the conversions and calculations on a separate sheet from the data entry would probably make things less likely to get broken.
posted by flabdablet at 12:21 AM on April 17, 2021 [1 favorite]

Response by poster: aniola-sorry, I misread your comment. I didn't think of Google Forms, because it's not always great with this kind of thing, but I tried it and it works well. Here's what it outputs. I just need to figure out how to convert that text into something more clear. flabdablet, I won't pretend to understand what you wrote but I'll look into that as well! thanks.
posted by zardoz at 12:46 AM on April 17, 2021 [1 favorite]

Best answer: I won't pretend to understand what you wrote

Broken down:

B4:G4 identifies the range of cells between B4 and G4 inclusive, which on your sample sheet are where the data entry for the sample word "bicycle" goes. On your sample sheet there's a 1 in cell D4 and the rest are empty.

arrayformula() is a function specific to Google Sheets (Excel doesn't have it) that allows you to apply single-cell functions like trim() and single-cell tests like ... > "" to whole ranges of cells at once; the overall result of arrayformula() is itself a nameless range of cells, suitable for further processing by functions that expect cell ranges such as match().

trim() takes a cell, converts it to text if it's not already text, and removes any leading or trailing spaces. So for empty cells, or cells containing only blanks, the result of trim() will be text of zero length; if there's anything visible in a cell at all, the result of applying trim()to it will be text with some length.

Text with any nonzero length compares as greater than the empty string ""; text with zero length doesn't. So the result of arrayformula(trim(B4:G4)>"") is a range of nameless cells the same size and shape as the range B4:C4, each of which contains true if its corresponding cell in B4:G4 has had something visible entered in it, or false if it hasn't.

Applying match(true, ..., 0) to that range searches it from beginning to end and returns the position of the first instance of true found within it - that is, the position of the first nonblank thing entered inside the range B4:G4, relative to the beginning of B4:G4. So for your example sheet, the first (and only) cell within that range that isn't blank is D4, which is the 3rd cell in the range, and the result of the match() is 3.
posted by flabdablet at 1:15 AM on April 17, 2021 [1 favorite]

Response by poster: thanks for the details! Btw all, the Sheet and Form are editable, so if you're so inclined, feel free to change!
posted by zardoz at 1:34 AM on April 17, 2021

Best answer: I added a new sheet named flabdablet, with a couple of columns of formulas like those above to derive numeric scores from responses entered on the Before and After sheets, then added fake responses for "framework" to test whether it works. Check it out before the entire internet decides to fiddle it into unrecognizability :-)
posted by flabdablet at 3:30 AM on April 17, 2021 [1 favorite]

Response by poster: flabdablet, I wish you were here because I would buy you drinks all night. Thanks!
posted by zardoz at 4:55 AM on April 17, 2021

Are you OK to take it from here, with comparing and averaging and charting and whatnot?
posted by flabdablet at 4:56 AM on April 17, 2021

Response by poster: With your tab I can see how the formula works. Or at least I can see the end results, if I'm not totally clear on the formula. I can make a simple column chart with that, I think, and that might be good enough. Thanks for asking. This will be a long project, and I'm just doing the baby steps now.
posted by zardoz at 5:28 AM on April 17, 2021 [1 favorite]

« Older Who designs the funky fountains at malls?   |   Should I eat this: Bread Pudding Edition Newer »
This thread is closed to new comments.