# Thread: Formula Help (Excel 97)

1. ## 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. ## Re: Formula Help (Excel 97)

Your example does not show how the responses are recorded.

3. ## 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. ## 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?

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

#### Posting Permissions

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