[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Survey Results in Excel Pie Chart

Posted on 2011-10-23
Medium Priority
Last Modified: 2012-05-12

I have online Survey from 10 question, and I exported the results into Excel spreadsheet.

What I need is to create Pie Chart for the 10 questions and for each question the answers is :

1) Excellent
2) Very Good
3) Good
4) Fair
5) Poor
I want to calculate the average answer of each question and show it in pie chart.

example :
first question , 7 answer was Very good + 2 Good + 1 Fair

I need to insert a bar with the question sentence with it's average answer.

How I can do that?

Thanks for any help,,
Question by:beesoo00
  • 2
LVL 93

Expert Comment

by:Patrick Matthews
ID: 37013885
Pie charts are a notoriously bad way to visualize data.

In addition, when your survey responses are categorical, the very concept of an "average" response is meaningless at best and downright misleading at worst.

Author Comment

ID: 37013889
Thanks Savant , so what you can give me advise ?
LVL 93

Expert Comment

by:Patrick Matthews
ID: 37014438
If it were me, I would probably make sure that my data were arranged like this:

Question      Response      Qty
1      1      204
1      2      322
1      3      79
1      4      1
1      5      395
2      1      282
2      2      235
2      3      120
2      4      228
2      5      135
3      1      229
3      2      116
3      3      67
3      4      346
3      5      243


Then, I would create a PivotTable with PivotChart, with Question as a report filter (page field in Excel 2003 or earlier), Response as row field, and sum of Qty as column field.

For the PivotChart, I would use a column or bar chart.

Then, by adjusting the value in the report filter, I can see the responses for any given question.

For more about PivotTables/PivotCharts, please see:

LVL 84

Accepted Solution

David Johnson, CD, MVP earned 2000 total points
ID: 37016714

What you need to do is was assign a weighting of:
10 for Excellent
7.5 for  Very Good
5  for Good
2.5 for Fair
1 for Poor

, 7 answer was Very good + 2 Good + 1 Fair

row 1 will have
0 7 2 1 0  @sum(a1:e1)  @sum((a1*10 + b1 * 7.5 + c1 * 5 + d1 * 2.5 + e1) /f1)
f1 gives you the total responses, and g1 gives you the weighted average

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question