Excel Formula: Calculate Survey Statistics

Question

I have a survey from 100 users and I'm trying to calculate some statistics. The relevant fields in my survey look something like this:

    Gender           Interests
B1: Male         D1: Running, Snowboarding, Mountain Bikes
B2: Male         D2: Programming, Running, Paintball
B3: Female       D3: Bowling, Gymnastics
B4: Male         D4: Rock Climbing, Running,

I need to calculate the % of Males that are interested in "Running". The text will always appear in the string exactly as "Running" but it may appear in a different order.

Here what I have so far:

=SUM(
     COUNTIF(
             D1:D100,ISNUMBER(
                               SEARCH(D1:D100,"Running")
            )
      )
 )

Notice I haven't factored in the Male/Female criteria yet. This expression is currently returning a 0.

Any help would be greatly appreciated.

Answer 1

An easy way to approach it would be to break up your calculations a little.

In another column use a formula like this:

=IF(AND(B1="Male",ISNUMBER(SEARCH("Running",D1))),1,0)

This will give you a 1 for everyone who is Male and has "Running" listed as an interest and a 0 for all others. Copy that all the way down your sheet and then it's easy to calc the percentage, for example if the column was E:

=SUM(E1:E100)/100

Answer 2

You can do a lot with Excel and ADO.

Dim cn As Object
Dim rs As Object
Dim sFile As String
Dim sCn As String
Dim sSQL As String
Dim s As String, f As String
Dim sa As Variant
Dim i As Integer, c As Integer
Dim r As Range

''This is not the best way to refer to the workbook
''you want, but it is very conveient for notes
''It is probably best to use the name of the workbook.

sFile = ActiveWorkbook.FullName

sCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open sCn

sSQL = "SELECT interests " _
       & "FROM [Sheet1$] "

rs.Open sSQL, cn, 3, 3

With Worksheets("Sheet2")
    s = rs.GetString(, , , ",")
    sa = Split(s, ",")

    c = 1
    For i = 0 To UBound(sa)
        Set r = .Range("a1:a" & c)
        f = Trim(sa(i))
        If r.Find(f) Is Nothing Then
            c = c + 1
            .Cells(c, 1) = f
        End If
    Next

    .Cells(1, 1) = "Interests"
    .Cells(1, 2) = "Male"
    .Cells(1, 3) = "Female"

    For i = 2 To c
        rs.Close

        sSQL = "SELECT Gender, Count(Gender) As GNo " _
             & "FROM [Sheet1$] " _
             & "WHERE Interests Like '%" & .Cells(i, 1) & "%' " _
             & "GROUP BY Gender"

         rs.Open sSQL, cn

         Do While Not rs.EOF
            If rs.Fields("Gender") = "Male" Then
                .Cells(i, 2) = rs.Fields("GNo")
            ElseIf rs.Fields("Gender") = "Female" Then
                .Cells(i, 3) = rs.Fields("GNo")
            End If
            rs.MoveNext
        Loop

        Next

End With

''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Answer 3

You should also test to see if this proportion (% of men that like running) is by fluke or not.

You can do a (Pearson or Likelihood Ratio) Chi-Square test to see if the proportions are different from expected.

You can also do Fisher's Exact Test to see, for example, if the proportion is different between genders.