2

I'm processing a bunch of results from a google forms result spreadsheet into an excel spreadsheet (making nice graphs and charts) but I cannot figure out how to process the multiple choice question answers in the way google provides them.

In the image below you see some example results. The rows represent one person who filled in the form. The column name is the question itself and in the cells you'll see the multiple answers a person selected (checkboxes).

response table

Now I would like to get an pivot table with the total percentage of times an answer is chosen.

I also made a small table with all the answer possibilities (see image below) but still can't figure out how to get the total number or percentage off occurrences of those answers in the other table.

all answers table

Can someone tell me how to make this pivot table in a fast en efficient way, i have a lot more of these type of answers and questions to process so i can't go and type every possible answer in a table.

So this would be the desired result:

desired result

Thanks in advance!

Jouke
  • 330
  • 4
  • 17

2 Answers2

3

Found the answer here: Formula in Excel to count occurrences of substrings within strings

Basically, you need to search for a substring within a list of strings. This is the equation to use.

=COUNTIF(D2:D7;"*"&<answers>&"*")
Community
  • 1
  • 1
Yin
  • 96
  • 6
0

I can't comment, but it sounds like you would benefit from using the Calculated Field feature of a pivot table. See this page Excel Pivot Table Calculated Field for more information. I would also suggest showing how exactly your data is stored. Is each answer in its own column? I hope so because that is much easier to deal with than the one column with the comma-separated Answer 1, Answer 2. Although, it's not too hard to create new formula-driven columns that separate the answers.

Back to the subject at hand, a formula in the Calculated Field would be something like:
Name: PercentageAnswer
Formula: =CountofQuestion/CountofPerson

Note that the Pivot Table should be formatted so that the rows are the "Question" header, with each row being an answer, and CountofPerson and CountofQuestion should be columns.

K. Long
  • 13
  • 5
  • The answers are comma separated as you can see in the first image, but is has been solved, thanks for your answer :) – Jouke Mar 09 '17 at 10:55