When working with your exports, you may want to know "how can I tell who selected this response and that one?" Data for each response option is stored in separate columns, so we need to create a new one which will tell us a simple yes or no as to whether our requirements have been met.
We will be working in the Master Data tab, which you can read more about here.
The key to understanding here is that a
1 in a column we are interested in is a positive response, and a
0 means that answer was not selected by that respondent. Each of the examples below will will make use of summing the responses and applying
OR logic depending on what we want to achieve.
Final note: these will work for responses within the same question, or different questions!
We will use this question for our example.
Example 1 - who selected this and that?
Aim: who selected 'Clubs' and 'Bars/ cafés'?
First things first - check your data map to see which question and response codes you want to look at. We'll then be focused on the corresponding columns in Master Data. In our example, we will be looking at 'Clubs' and 'Bars/ Cafés' - so we're interested in response codes Q3_R2 and Q3_R3.
Below are the columns in Master Data. We have added a new column (to the right of demographics) where we will input our formula. The columns we are interested in are highlighted.
If a respondent has selected 'Clubs' then there will be a
1 in column AJ; the same applies to 'Bars/ cafés' and column AK. Therefore if both options are selected, we would expect a total of 2.
For this example, the formula in the new column is
The output of this will either be
FALSE because we are saying that one thing (the sum) is equal to another (2).
This method can be expanded to look at any number of answers, either within the same question, or from different questions, you just need the response codes to find the relevant columns.
Example 2 - who selected this and not that?
Goal: who only selected 'Home'?
Here's a reminder of the data map.
This time, we want:
Q3_R1 to be
All others to be 0
We can ignore skips, as if skip is
1, it is impossible to answer the question, so there will be a
0 in all other columns for this question.
Here, we will use the
AND formula which returns
TRUE if all conditions are met, and
FALSE if not.
This time, the formula in the new column is
AND formula checks two things:
AI16=1to see if Q3_R1 was selected
SUM(AJ16:AM16)=0to see if any other options were selected
When both are true,
TRUE is shown.
Example 3 - who selected this or that?
Goal: who selected "Home" or "a friend's place" (but not necessarily both)?
Here's another reminder of the data map.
This time, we want:
Q3_R1 OR Q3_R4 to be
1; both can be
1, so the total will be 1 or 2. We can write this as
Other answers do not matter.
We can use
SUM again to check the total of both responses, but instead of using
equal to, we want to check whether the total is
greater than or equal to 1.
Into the new column, we enter this formula:
If neither option is selected, the total will be
0 and show
If only one option is selected, the total will be
1 and show
If both options are selected, the total will be
2 and also show
Check and double check
If you're doing this for the first time, be sure to check these areas if things don't look quite right:
1. Are you checking for the correct totals?
If you want one thing AND another, your totals should be the same as the number of answers you are checking. For example, for 2 answers the total should be 2; for 3 answers the total should be 3 and so on.
If it's one thing OR another, you will want a minimum total of 1.
2. Are you using SUM correctly?
Make sure you know the difference between
Using a comma will just sum the two selected cells, but a colon will sum them as well as every column or row in between.