Saturday, June 29, 2013

Google Spreadsheet Query For Displaying Pie Chart

Question:

(by Matt)

Morning All,

I've been looking at this for the last hour and have managed to get myself quite confused.  I have a Spreadsheet that looks like this (example only):



This is data collected as a result of a Google Form Survey.  In it we asked users from the business "Have you ever downloaded apps from Google Play?".  The second question is "Did you find anything useful for work purposes?".  After sending out the questionnaire I realised that some users are answering 'No' to the first [required] question and then answering 'No' to the second [optional] question, which is affecting the results slightly

What I would like to do is create a pie chart which displays - where users have said 'Yes' they have downloaded apps from the apps store, how many of them found an app useful for business. Something, perhaps, along the lines of a field which contains COUNT 'Yes' in column B where column A also has a 'Yes'.  Also, COUNT 'No' in column B where column A also has a 'Yes'?

The responses to the survey are still coming in so I would like this to be dynamic.  I started creating a new sheet and tried to use Array formula to create a table form which to derive the pie chart, but have ended up getting very confused.

Can anyone point me in the right direction?

Thanks
Matt


Solution:

Make a new sheet, and in this sheet put the following formula in Cell A1:

=query('Sheet1'!A:B;"select B,count(A) where A='Yes' group by B order by B desc label B '',count(A) ''";0)

I am assuming that your first sheet's name is 'Sheet1', or else replace 'Sheet1' with your sheet's name.

Now you will get the result as you can see in the screenshot below:



And now you can insert pie chart, and thus you can show the data in form of a chart.

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

1 comment:

  1. Great post! I'm wondering if you can do this count with the criteria being if a certain text string is contained within the cells, rather than checking for a whole cell match. I.e., I have two columns of data, and cells in both can contain multiple criteria (they are generated from checkbox inputs from a Google Form). I want to total up how many times X and Y were both checked.

    Thanks much!

    ReplyDelete