Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Aug 2004
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Help (Excel 97)

    I sent out a survey and am now trying to calculate my results. One of the survey questions was "Rank your top three considerations in choosing a lawyer". I want to determine what response was given most often. Is there some way I can write a formula for this? I have attached an example.

    Thanks for any suggestions!

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help (Excel 97)

    Your example does not show how the responses are recorded.
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Aug 2004
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help (Excel 97)

    I edited the spreadsheet so I hope it is more clear. The first row has the companies and below is their response.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help (Excel 97)

    Ok, I think I understand that. Now, what do you want to count? The number of times "Experience" appears in the answers? Does "Experience" appearing in the first line carry more weight than if it occurs in the second or third line?
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Aug 2004
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help (Excel 97)

    I would like to know which answer appears the most often. Order doesn't matter unless there is a tie, which I don't think there is. Thanks so much!

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help (Excel 97)

    The User Defined Function below takes a stab at this. The attached workbook shows an example of how it is used in columns V:W.

    <pre>Public Function CountResponse(strAns As String, oRng As Range) As Long
    Dim oCell As Range
    Dim lCount As Long
    lCount = 0
    If Not oRng Is Nothing Then
    For Each oCell In oRng
    If InStr(oCell, strAns) > 0 Then lCount = lCount + 1
    Next oCell
    End If
    CountResponse = lCount
    End Function
    </pre>


    However, there is a problem with the way your results are entered. Each word in the responses have spaces between them. There is no way that a formula or UDF can determine whether "Service Value" is one response or two. You will need to enter your data differently to get what I think you want. You will either need to enter each response in a separate cell (C1:C3 for Company A for example), or use a different character between answers (Alt+Enter for example) than you use between words.
    Legare Coleman

Posting Permissions

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