Cell Formula - Percentages

Posted by Andrew Arndt on May 14, 2001 10:13 AM

I am in the process of taking a survey and entering the data into an MS Excel Spreadsheet. The answers for this survey RANK a question for 1-21, based on importance. How can I create a formula which will 'chart' the RANK in a graph?

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

Posted by Mark W. on May 14, 2001 10:30 AM

Andrew, enter your data as an Excel list...

{"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.

Posted by Andrew Arndt on May 14, 2001 12:25 PM

I tried the suggestion, but was unable to get any results.

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

Posted by Mark W. on May 14, 2001 12:58 PM

Care to provide some respresentative data? Sure
makes it easier on us!

Posted by Andrew Arndt on May 14, 2001 1:12 PM

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

Posted by Mark W. on May 14, 2001 1:41 PM

Okay, suppose that your data is in cells A2:F6 like
shown below:

{"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.

Posted by Andrew Arndt on May 16, 2001 12:47 PM

Thanksfor the assistance! With a couple minor modifications your formula (array) worked great!

I wish I had the time to understand all the formula combinations available...

Thanks again!!

Andrew