OUseful.Info, the blog…

Trying to find useful things to do with emerging technologies in open education

Creating Your Own Results Charts for Surveys Created with Google Forms

Just before Christmas, I volunteered to put together a questionnaire for a course on ICT (T209) that would informally and anonymously collect information from students about their mobile phone usage; the idea being that we could run similar surveys in the follow on course (T215 Communication and information technologies) in future years and give a students’ eye view of trends in behaviour around network connectivity and mobile devices; (the courses have large populations, so the results are potentially statistically significant).

The tool I suggested was Google Forms, partly because it’s likely to have some sort of longevity (for the number of respondents we expect, we would have to pay to make use of something like SurveyMonkey), partly because I couldn’t find a nice locally hosted survey tool (I did ask…).

Creating forms is easy, as this mini-tutorial suggests: Introduction to Google Forms.

As you would expect, different question types are possible:

One really nice feature of Google Forms – experimental at the time of writing – is an automatically generated summary results form, that where possible provides chart-based summary views for each question in the original form.

However, to view the summary charts (at the moment at least) you need to sign in with a Google account… which was one step too far for the course team who wanted the survey…

So the workaround I came up with was to create my own charts to display the results, using the data collected from the form, which is stored in a spreadsheet document.

- raw data:

- self-created charts:

(The results differ because the screenshots were captured at different times. The mix of chart styles is to provide groundwork for potential exercises regarding effective presentation and appropriate chart style selection for different sorts of data.)

So – how do you create your own results? I found the trick here:

I have a form on a spreadsheet and have a colum that is populated from some checkboxes in the form. This means I have a “, ” separated list of answers for each cell in this column.

COUNTIF(column,value) seems to only count rows where the only checkbox selected was value, not where other values are also in the list in the cell.

What function(s) can I use to properly count values as produced by the checkbox option from a form in spreadsheets?

Answer: (from user ahab)
Try this method:
B2: apples,grapes,pears [results in cell B2, etc.]
B3: oranges,apples,grapes,pears
B4: apples,pears

To find the number of times “grapes” were among the selected fruits:
=ARRAYFORMULA(COUNT(IFERROR(FIND(“grapes”;B2:B))))
As you may be using this formula on another sheet than the sheet the submitted data is on, don’t forget to prepend the range B2:B with a sheet name like this: ‘Sheet1’!B2:B

[The formula works as follows:]
=ARRAYFORMULA(COUNT(IFERROR(FIND(“grapes”;B2:B))))
ARRAYFORMULA this function allows array expressions and functions -which are cell-only oriented – to be applied to ranges; in this case to enable FIND to be applied to each row in the range B2:B . [That is, from cell B2 to the end of column B.]
COUNT this function counts the elements of the range it is presented with – a bit as one would expect it to do ;)
IFERROR this function evaluates its first parameter – if the first parameter evaluates OK this value is returned – if the first parameter returns an error an -optional- second parameter to IFERROR is evaluated. In this case there is no second parameter so on error nothing is returned.
FIND(“grapes”;B2:B) this function is the first and only parameter to IFERROR FIND looks for “grapes” in each row of the range B2:B – but is only able to do so because of the ARRAYFORMULA wrap. Without it FIND would only be able to search the first element in the range B2:B ; i.e. only in B2 only. When FIND doesn’t find “grapes” it produces an error.
So the range – allowed by the ARRAYFORMULA wrap – FIND finally produces would be something like:
8
16
#NA
Because of the IFERROR function wrap the #NA error is filtered out, producing the range
8
16
The COUNT function counts these elements: 2

The approach I took to counting the results was as follows – itemise the results options by copy and pasting the results options for each question from the survey form, and then count the number of the occurrences using the formula described above, with the filter term pulled in from the corresponding label:

Just by the by, there were a couple of gotchas – for example, for questions with numerical answers, “0” matched empty cells as well as actual “0” answers – so I renamed “0” as “Zero”.215

Written by Tony Hirst

February 17, 2009 at 10:04 am

Posted in CandS_HowTo, Tinkering

9 Responses

Subscribe to comments with RSS.

  1. [...] millor però més complexa que SurveyMonkey. Tony Hirst, OUseful Info. (Informació útil) [L'enllaç] [etiquetes: Google, [...]

  2. [...] Tinkering , Visualisation Tags: Many Eyes A couple of weeks ago, I posted a workaround for Creating Your Own Results Charts for Surveys Created with Google Forms. With the release of Many Eyes Wikified, it’s now possible to power Many Eyes visualisations [...]

  3. I think Google docs is a great way to quickly and easily collect data from a variety of sources. It can also be used for basic simple surveys which for most DIY’ers will suffice. Wouldn’t recommend it for business applications. I just had a quick run through so I could get a feel for the process and have documented it here if you want to have a look. http://www.rockresearch.co.nz/blog/2009/09/01/using-google-forms-for-online-market-research/

    Jared

    September 1, 2009 at 2:35 am

  4. Thanks for this Tony. It’s incredible how little information exists about analysing the data collected in google forms in any other way than the included summary tool (which really doesn’t get you very far). I have spent the last few hours looking around and this is the most useful article on the topic I was able to find.

    There are hundreds of generic “google forms is easy and great” articles, including the one posted in the previous comment (which seems like spam – i saw the exact same comment on various blogs talking about google forms).

    Anyways, thanks for recording and sharing everything you do. Your blog is an incredible educational resource for the community. P

    Philipp Schmidt

    October 30, 2009 at 3:52 pm

  5. Man you saved my day. Trying to get a chart of that bloody form for about three hours :) Was prepared to go mysql :)
    tx

    cristian ditoiu

    November 3, 2009 at 5:50 pm

  6. [...] to view the results meant I need to come up with a workaround for producing results charts (Creating Your Own Results Charts for Surveys Created with Google Forms). It’d be easy enough to mbd this charts in th VLE, but that would be competing with the [...]

  7. thanks for this! I used it today, and found that the IFERROR part was throwing me errors, and when i took it out everything worked as it should. thanks!

    jimmy p

    January 31, 2011 at 4:59 am

  8. This is great! but i found that when i have a word within a word, it seems to mess up the counts

    example Agree, Disagree shows a total count of 2 for the word “Agree”. To resolve this i used

    =COUNTIF(A1:A100,”Agree”). One can also replace the word “Agree” with the cell reference details as shown below.

    =COUNTIF(A1:A100,B2)’

    Best Regards,

    Maria C

  9. Hi there to every body, it’s my first pay a visit of this blog; this
    blog carries remarkable and in fact excellent data for visitors.

    Adalberto

    December 18, 2013 at 12:45 pm


Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 819 other followers

%d bloggers like this: