Results 1 to 6 of 6
  1. #1
    Star Lounger
    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!!!

  2. #2
    Star Lounger
    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

  3. #3
    WS Lounge VIP sdckapr's Avatar
    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 non-empty 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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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 25-May-05 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

  5. #5
    Star Lounger
    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>

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •