Search code examples
excelstatisticsformulasurvey

Excel Formula: Calculate Survey Statistics

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.

Solution

  • 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