Google Sheets data: how to input, how to extract data.

Post date: 2021-04-17 19:33:03

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.

Please click Here to read the full story.