Web 2.0: Digging Deeper into Google Sheets
Background:
This is a part of a web log for a class I am taking on 'Computers, Problem Solving, and Cooperative Learning'. This post is one in a series of reflections on Web 2.0 tools and how they can be used to engage students in critical thinking as 'mindtools'.
Other posts from this web log:
Spreadsheets
Spreadsheets have been around for quite a while, I used them occasionally while I was in school and now occasionally in my work life. Majority of the spreadsheets I work with currently are on Google Sheets because they great for organizing all the systems, hardware and software that are in our school. We also work with multiple people on different teams, so having a cloud based platform eases up collaboration and contribution.
When spreadsheets were introduced in class as a mindtool, it took me a while to come around to understanding how they could enhance critical thinking. Everyone in our class was invited collaborate on a shared Google Spreadsheet to collect and analyze data on the same topic of 'Poverty in America'. Initially I simply found some statistics online about the highest to lowest categories of poverty in the U.S. and turned it into a chart.
When used as a mindtool, databases help learners integrate and interrelate discrete bits of content, making them more meaningful and more memorable. -Kirschner and Erkens (2006)
When I began reflecting on how Kirschner and Erkens (2006) describe databases are used as mindtools, I didn't think that the contribution I made really pushed my thinking in the way that a mindtool should. I started reflecting on some of the other features that available on Google Sheets and thought about the many times I used a spreadsheet to store information I was collecting through a Google Form. This led me to think if there was any type of information I could collect through my social networks. I wanted to see if many people actually knew what the poverty rate was in the United States or if perception was higher or lower. What gave me this idea was this YouTube video which looks into what people perceive is the wealth distribution in America compared to the reality.
The Google Form
I made a form and put in just one question, "What do you think is the percentage of people living in poverty in America?". Upon reflection, I think I would have added some questions to find out more about the population that was answering my survey. For instance, where they were in the world, age, gender, and perhaps what their income bracket was. This would have given me more insight into the responses if I had known who was answering the question! Between my Twitter, Facebook, and Google+ networks, I managed to get 41 responses over 2 days.
The Data
As the information came in I began thinking about how I could use Google Sheets to interpret the data. I began by setting up an area for summary data and set up some formulas to interpret the information as more numbers came in. I included the following formulas:
- Number of responses: =COUNT(B2:B)
- Average of responses: =AVERAGE(B2:B)
- Most common response: =MODE(B2:B)
- Number of responses above actual poverty rate in 2014: =COUNTIF(B2:B,">14.8%")
- Number of responses below actual poverty rate in 2014: =COUNTIF(B2:B,">14.8%")
- Number of responses within 1% of actual poverty rate in 2014: =SUM((countif(B2:B,"=16%")),(countif(B2:B,"=15%")),(countif(B2:B,"=14%")))
With those formulas in place, the summary data updated automatically whenever a new response came in. The formula I found the most difficult to figure out was the last one. I was trying to use =COUTIFS and then make 2 conditions apply to column B, so that if the number fell between a range it would count, but it kept giving me a "0" as result when I knew there were responses that were between the range. So I did it the long way and had added up all the instances of each possible response. I also rounded up 14.8% to 15% knowing most people would respond with a whole number. Here are the final results after I closed the form:
Visualizing Data
The last piece that I wanted to show was a graph which once again adjusted as the data came in. I didn't know how to get the line for the actual poverty rate without typing in the poverty rate into each and every cell in column C where there was a response in column B. If anyone has a solution to this let me know!
Finally, here is the line graph that formed:
Conclusions
It turns out most people think that the poverty rate is much higher than it actually is. It made me wonder what factors affect this perception. If I had to continue with this project I would probably do the survey again but this time edit the form to include data about the people who were submitting responses. This way I would have more information to compare and analyze. Like perhaps, how many of the participants were American, how many of them actually live in the United States, perhaps there might be patterns in perception correlated to age? It would definitely have more avenues for exploration with more pieces involved! If you could conduct a survey what would questions would you ask on Google Forms to promote learning and critical thinking? After this mini-project I can now see how Google sheets is a mindtool that pushed my thinking further!
References:
Kirschner, P. A., & Erkens, G. (2006). Cognitive tools and mindtools for collaborative learning. Journal of Educational Computing Research, 35(2), 199-209.