Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
IE.,(10 responses) Q1: 4,5,6,5,4,4,4,4,5,5
I am looking for a result such as: Q1: 4=5 responses (50%), 5=5 responses (40%), 6=1 response (10%)
Any ideas on how to work this problem?
Thanks
{"Question","Response"
;1,4
;1,5
;1,6
;1,5
;1,4
;1,4
;1,4
;1,4
;1,5
;1,5}
...summarize it using a PivotTable, and then
create your chart from the PivotTable.
Your PivotTable would be created by putting
'Response' in the COLUMN area, 'Question' in the
ROW area, and 'Count of Response' in the DATA
area applying the "% of row" Option.
Let me see if I can explain the problem a little clearer.
Example
I posted a survey and asked in what priority an individual should perform a task. There are 5 items: crawl, kneel, walk, jump, climb. Each respondant answers these questions and 'ranks' (in thier opinion) what happens first.
Taking that Data, I would like to know how many people said crawl first, OR how many people picked crawl second...etc.
I have listed that Data in columns with the question at the left of the row.
What formula can I 'total' the "1" results, "2" results, etc.?? And is this possible?
Thanks.
Andrew
Question: Answers
Crawl: 1,1,2,5,1
Kneel: 2,3,1,4,5
Walk: 4,4,4,3,2
Jump: 3,2,3,1,3
Climb: 5,5,5,2,4
Looking to creat results like:
Crawl:
1:3 [Then indicate percentage] 60%
2:1 [" "] 20%
3:0 [" "] 0%
4:0 [" "] 0%
5:1 [" "] 20%
Total 100%
Kneel:
Same
Etc...
I have listed that Data in columns with the question at the left of the row.
What formula can I 'total' the "1" results, "2" results, etc.?? And is this possible?
Sorry about the confusion & thanks again.
Andrew
{"Crawl:",1,1,2,5,1
;"Kneel:",2,3,1,4,5
;"Walk:",4,4,4,3,2
;"Jump:",3,2,3,1,3
;"Climb:",5,5,5,2,4}
Do the following:
1. Enter "Crawl:" in cell A9
2. Select cells A10:A14, type ={1;2;3;4;5}, and
press the Control+Shift+Enter keys in combination.
3. Enter the array formula,
=SUM((INDEX($B$2:$F$6,MATCH($A$9,$A$2:$A$6,0),0)=A10)+0) ,
into cell B10 while pressing the Control+Shift+Enter
key combination, and then Copy down to cell B14.
4. Enter the formula, =B10/5, into cell C10, format
this cell as 0% and Copy down to cell C14.
5. Enter the formula, =sum(C10:C14), into cell C15
6. Select cells A9:C15, Copy, and Paste onto cells
A17:C23.
7. Change the contents of cell A17 to "Kneel:"
8. Change the 1st argument of the MATCH() function in
cell B18 from $A$9 to $A$17, press the Control+Shift+Enter
key combination, and Copy this revised formula down to B22.
9. Repeat steps 6 thru 8 until all Questions have
been summarized.
I wish I had the time to understand all the formula combinations available...
Thanks again!!
Andrew