Results 1 to 6 of 6

20050525, 16:01 #1
 Join Date
 Aug 2004
 Posts
 57
 Thanks
 0
 Thanked 0 Times in 0 Posts
Survey Results Formulas (Excel 97)
I need some help writing a couple of formulas. I am creating a spreadsheet of survey results from a seminar.
1. One question asked users to either check 'satisfactory' or fill in a comment. I want to create a formula which says out of X number of surveys sent out Y number answered 'satisfactory'. I am not sure the exact number or surveys sent out so I want the formula to total the number of surveys sent and tell me out of the total, how many people answered S for satisfactory. The result would look something like 10/50. I am open to other suggestion if you have any.
2. One question asked who have you had contact with A (Assistant), M (Manager) or O (Other). I want a result which totals the number of A, M & Os all in one cell but divided up something like A = 3, M = 8, O = 1.
3. One question asked users to rank 3 considerations when selecting a company. There were 9 options. I want to know what the top 3 overall were.
Any help would be greatly appreciated!!!

20050525, 17:16 #2
 Join Date
 Aug 2004
 Posts
 57
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Survey Results Formulas (Excel 97)
Thanks, I am off to a good start.
1. The first part of the formula worked. Is there a way to count the number of cells in a range. Some of these cells are blank. I need to know the total number of cells between D14 and AS14.
2. Some of the cells have two results for example A M. In this case, the formula did not count the A or M. Is there a way to enter the text or write the formula to count these examples?
3. See attached for an example

20050525, 17:38 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Survey Results Formulas (Excel 97)
<img src=/S/blush.gif border=0 alt=blush width=15 height=15>I made a typo, you probably want to use CountA, not count (they are text)
=counta(D14:AS14)
will give a count of the nonempty cells
=Columns(D14:AS14)
will give the count of the cells whether they have anything or not
=count(D14:As14)
will give a count of cells with numbers
2)use wildcards
=COUNTIF(D14:As14,"*A*")
=COUNTIF(D14:As14,"*M*")
etc
3) You could get the 9 counts:
=COUNTIF($B$2:$B$100,"*A*")
=COUNTIF($B$2:$B$100,"*B*")
...
=COUNTIF($B$2:$B$100,"*I*")
Then use large(rng,1), large(rng,2) or large(rng,3) to get the top 3 values, where rng is the range of cells containing the 9 counts.
If you had an attachment with the data filled out I could be more specific...
Steve

20050525, 17:39 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Survey Results Formulas (Excel 97)
<P ID="edit" class=small>(Edited by sdckapr on 25May05 13:39. Changed COUNT to COUNTA, as the cells contain text)</P>1) Could be done with a Countif divided by a count.(Change range as appropriate)
=Countif(A1:A100,"S") & " / " & CountA(A1:A100)
2) combine 3 countif
="A = " & Countif(A1:A100,"A") &", M = " &Countif(A1:A100,"M")&", O = " &Countif(A1:A100,"O")
3) I would have to see the setup to understand better.
Perhaps you could post a sample file with some made up results...
Steve

20050525, 18:02 #5
 Join Date
 Aug 2004
 Posts
 57
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Survey Results Formulas (Excel 97)
Thanks. I've managed to make the whole thing work. I really appreciate your help. I am getting a much better grasp of formulas! <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

20050525, 18:30 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Survey Results Formulas (Excel 97)
I am glad I could help...
Steve