How to chart checkbox-type responses

[Jesse Thorstad]

A teacher gave a survey to students designed as a Google Form.

Some of the questions are checkbox-type questions, allowing for multiple items to be selected.

For instance: What motivates you?

Options: Grades; parental pressure; money; peers, etc.

As you all know, all of the responses are grouped into once cell, comma-separated.

This does not allow for useful charting. Even using a pivot table, I'm having a heck of a time making a graphical representation of responses palatable. 

I suppose a script can be used to break each of the comma-separated responses out into individual rows?

Anyone have any experience graphing this kind of data? It makes my head hurt.

Jesse Thorstad
Technology Specialist
Fergus Falls (MN) Public Schools
http://www.isd544.org
218-998-0544  x2036

[Joel Lowsky]

The easiest method for this is to export the results to Excel, and use Text-to-Columns in Excel to break out the comma-separated entries using the comma as a delimiter.

Google Spreadsheets doesn't offer text-to-columns yet, though I think I read that the New Sheets might incorporate it.

Here's a way to do it with Script.

http://www.googlegooru.com/text-columns-google-spreadsheets/

--
--
You received this message because you are subscribed to the Google
Groups "Google Apps K12 Technical Forum" group.
To post to this group, send email to k12ap...@googlegroups.com
To unsubscribe from this group, send email to
k12appstech...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/k12appstech?hl=en?hl=en

---
You received this message because you are subscribed to the Google Groups "Google Apps K12 Technical Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to k12appstech...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Devon Jacobs]

If you want to keep it in Google sheets, it's not hard either... once you have the magic formula. 

I looked for quite a while, and for the life of me can't remember where I found it. Basically you do a find for all instances of the response you are looking for in the 'Form Responses' data sheet. 

For the project I was working on, I created a form with a single "where are you from?" question listing every US state. (and a second question for countries) Students could then choose any state they had ever been to, and every country, and submit. the results can be shown real-time using the google map chart types on separate sheets in the same google 'form responses' spreadsheet. once you have the data from the formula you could really use any chart type.

• I made a new sheet called data in the Form Responses worksheet
  
• copied the options (countries or states) into column A
  
• copied this formula into column B for data points (States):
  =ARRAYFORMULA(COUNT(IFERROR(FIND(A1;'Form Responses'!$B$2:$B))))

• created a new sheet called US States, and told Google to create a geochart using a US map
  
• Repeat for countries (much harder as you have to have an exact spelling / punctuation match to the countries Google recognizes in the map)

[David Tess]

I'd use the =split() function to separate the answers, and then =countif() to get your totals. You'll see what I mean in this example. Most of column B has the split function, and B12 through E12 has the countif.

Dave

[Chris Franzen]

I thought there was an add-on for it, but there is at minimum a script:  http://www.googlegooru.com/text-columns-google-spreadsheets/

--

Chris Franzen

chrisf...@nokomis.k12.il.us  

Technology Coordinator | Nokomis School District

511 Oberle St. | Nokomis, IL  62075

QR
Please consider the environment before printing this email.