Survey Reporting

SteveDCYF

I have created a survey and activated a form which is now being completed. The data is coming back into the sheet, but now I need to report on that data and hopefully build a dashboard for all of it. How can I report on qualitative information? The choices are correct or incorrect for each of 25 questions across maybe 80 respondents and I want to be able to see the % of correct vs. incorrect for each answer so I can develop a training curriculum around that. Any thoughts?

Jamie Ide

You could do a count of each question for correct and incorrect.

Then you can divide each by the total for a %.

Then you can display this as a widget on the Dashboard - perhaps a stacked bar chart to see the % for each question pretty easily.

SteveDCYF

@JIDEATTURRA do you mean create this through a report, summary sheet or other?

Jamie Ide

You could also create a report and filter it by correct / incorrect and group by Question.. but this wont display as a widget on the Dashboard.

You can create a sheet and do the following: Have a question column on the left with:

Question 1

Question 2

Question 3

Question 4

etc.

Then have a text/number column next to this called - Correct

=CountIF({Cross Reference the question column in the original sheet}, "Insert Correct Answer")

Then have a text number column next to this called - Incorrect

=CountIF({Cross Reference the question column in the original sheet}, "Insert Incorrect Answer")

Then have a column next to this text/number which is a % column (change this from the toolbar by clicking the % icon) called % correct:

=Correct@row/Incorrect@row

Now you all of the questions, the number correct and incorrect for each, and the % the correct makes up.

You can add a chart to a dashboard using this data.

If this helped, please upvote, it helps me.

Jamie Ide

You can also click the sheet summary icon on the right toolbar of the original sheet and add a field called

Question 1 Correct

The formula in this would be =CountIF([Question 1 Column]:[Question 1 Column], "Insert Correct Answer")

Then repeat this for all the questions.

Then you can create a summary report using these fields as columns.

If this helped, please upvote, it helps me.

SteveDCYF

@JIDEATTURRA that was my worry that I would have to do the q1,q2,q3, but if that's the only way, I understood all of that. And I also understand the % column. I just wish there was functionality in SS that did all this for me! lol… I guess I overstated the capability. Thank you

Jamie Ide

For a metric to display it on the Dashboard, you'd have to do that.

To just get an overview of it you could just create a report and group by question, then summarize the total of each answer to see an overview of how many people got right vs wrong.. this would then be automatic.

SteveDCYF

fyi the question1, 2,3, etc. need to be on columns in order for the chart to pull it. It didn't like the rows being the questions "could not chart." I think it has to be on the columns so I just copied the data sheet so I can calculate correct, incorrect, etc.

Jamie Ide

I can have questions 1 through 25 as a list (no need for 25 columns), then have two count columns to the right (correct / incorrect) and chart it.. with different colors for the columns based on correct and incorrect.

Jamie Ide

Make sure the count column is number and you havent added text into it else that will cause issues displaying on a Dashboard!