Formula Help (Excel 97)

Topic

New Reply

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!

Reply | Quote

Replies

Your example does not show how the responses are recorded.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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!

Reply | Quote

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.

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

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.

Reply | Quote