One way would be to list all the responses, then calculate that W% said
fair, X% said about right, Y% said good, and Z% said wicked ill (I have
teenagers in the house). This makes a pie chart or stacked column or bar
chart.
Here's an easy way to tabulate and compute the percentiles. Put a label
in a cell, like "Rating". List all the ratings under this cell, like this:
Rating
good
fair
good
about right
excellent
fair
good
about right
about right
good
excellent
good
about right
good
good
about right
Select this range, and choose Pivot Table from the Data menu. It won't
be a large pivot table, so put it in the same worksheet, when the wizard
asks you, maybe a few columns to the right. The Pivot table is initially
blank; drag the Rating field from the pivot table field list to the row
area, then drag it again to the data area. It will probably give you a
field button labeled "Count of Rating", and the whole thing will look
like this:
Count of Rating
Rating Total
fair 2
about right 5
good 7
excellent 2
Grand Total 16
You can make your own formulas to determine the percentages, but why
bother, when Excel will adjust the pivot table. Double click on the
Count of Rating button, Click on the Options button of the Pivot Table
Field dialog that appears, and where it says Show Data As, scroll down
and select Percent of Total. The result:
Count of Rating
Rating Total
fair 12.50%
about right 31.25%
good 43.75%
excellent 12.50%
Grand Total 100.00%
Alternatively you could assign arbitrary values to the labels, like
fair=40%, about right=60%, good=80%, and excellent=100%, then state that
the average score was 71.3%, or almost "good".
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
