Automating Analysis of Survey Results

Hi,

I'm working on a spreadsheet to process the results from a survey. I have attached a Excel file that contains simple examples of the sorts of data I trying to process across 3 worksheets.

The first worksheet is represented by the section titled "Type of Respondee". In this section, people describe how they identify from a number of options. Note that it is possible for people to input one, multiple, or zero fields - in this instance most respondees identify as either a pilot, dr, or chef, but one person identifies as a Dr AND chef, and one person identifies as a pilot AND dr AND chef (wow!). There are also two people who have left this blank.

The second worksheet is represented by the "Responses" section. Here, each of the people have given a free text response to a question. A human reads the free text response and identifies if any of the three themes are present in that response, marking it with a '1'. Responses may match with one, multiple, or zero themes. For example, the first response (by a pilot) is only fruit related. However, the last response (by a chef) is both fruit and sweets related. Some people haven't provided a response at all, and some have provided a response that doesn't match any theme (the response talking about a dog).

I am working on the third component, represented by "Analysis". Here I am aiming to make a breakdown of the number of times a theme has appeared in responses, as well as what type of person has raised them. At the moment this is done by me manually going through each theme and counting how many pilots, drs, chefs, or other, have given a response - quite time consuming. Note that if a theme is logged against a response that does not have an identifier in worksheet 1, then it is logged in a new field 'other'.

How can I use a formula to automate this calculation? I have thought about the process I use manually, which is:

1) Check if there is a response
2) Check if fruit is marked.
3) If yes, check if pilot is marked.
4) If yes, increment pilot/fruit by one.
5) Check if dr is marked.
6) If yes, increment dr/fruit by one.
7) Check if chef is marked.
8) If yes, increment chef by one.
9) If nothing is marked, increment other by one.
10) Check next response, etc etc/

This would repeat for Vegetables and Sweets.

However, I am not sure how to implement this sequential logic, or how to optimise this best for Excel. Any suggestions would be greatly appreciated as it has given me grief for a few days now. If I can provide any more information, please let me know.

Regards,

Robert

Sorry Glenn.

I use O365, I have amended my profile accordingly.

Try this in O3,then copy down and across.

Please Login or Register  to view this content.