How to Analyze TRUE/FALSE coded Multiple Response Survey Data in Excel Pivot Table Explained

This video you will walk you through how to analyze multiple choice survey data where the response appear in separate columns and each selection is coded as YES/NO.
BONUS: Learn how to divide the count of the responses by the Number of Respondents for that question inside a pivot table.
Check out these videos on multiple response survey data
1. How to Analyze Space-separated Multiple response Survey Data in Excel Pivot Table:
• How to Analyze Space-s...
2. How to analyze comma-separated multiple choice survey data in Pivot Table:
• How to analyze comma-s...
3. How to Analyze Columns-separated Multiple Response Survey Data in Excel Pivot Table:
• How to Analyze Columns...
4. How to Analyze 1/0 coded Multiple Response Survey Data in Excel Pivot Table:
• How to Analyze 1/0 cod...
5. How to Analyze TRUE/FALSE coded Multiple Response Survey Data in Excel Pivot Table
• How to Analyze Yes/No ...
SUBSCRIBE NOW and stay up-to-date in this series and more

[@MSurveyPoint]

Connect with me if you need support on the following services Email: [e-mail] Book appointment here 👉 calendar.app.google/MMasAbZPDuwCCux76 1. Survey questionnaire design 2. Online survey scripting 3. Survey data Analysis 4. Research report writing 5. Data analysis in Excel 6. Survey Dashboard in Excel 7. Survey Dashboard in Power Bi The scope of my services includes but not limited to the areas of Market Research, Customer Experience Research, Product Research, User Experience Research, Employee Engagement Surveys, NPS, CSAT & Voice of the customer, Monitoring & Evaluation, etc.

[@ahmedabdulahimahamud3272]

This is very very helpful, and your presentation is awesome

[@trevorkaseke9317]

I've been following your series and really learning a LOT! Thank you very much! I am struggling with one thing though: I have a question which has 10 statements, and respondents had to respond whether the statements were true or false. I have followed your tutorial and can determine how many answered what for each statement. My challenge though is this: For each of those statements, there is a right and wrong answer. Using the same analysis I have learnt from you, I can tell the percentage of respondents who got each statement correct/wrong. What I want to do now is to analyse how many respondents got ALL questions correct/wrong...how many got 75% correct/wrong...and so on. Any guidance on how I can do that?

[@MSurveyPoint]

When you load to power query editor 1. Select all the columns with the 10 statements 2. Under Replace values: Replace all TRUEs with 1s and FALSEs with 0s 3. Change the variable type of each of the columns to 123 type (you can do this by clicking on the left corner of each column head) 4. Add a custom column and name it "Exam Score (marked out of 10)" =Divide ((Sum[statement 1, statement 2,..... statement 10]), 10) Noted: check how to add measures in power query for the appropriate function What you get here will tell you the percentage of all the statements each respondent got correct (e.g. 100% means the respondent got all correct and 25% means the respondent got 75% wrongs,.... and so on 5. You may change the type of the column in point 4 to ABC type (so it becomes a categorical variable) 6. Close and load and add to data model 7. Generate a pivot table like in previous videos with Exam score in row field and ID in column field 8. This should give you the counts for each Exam score. (You can even add the ID again and take percentages for interpretation) 9. If the Exam score field was numeric, you can right click that in your pivot table and group the scores (to say 0% - 10%, ..... and so on) I hope this helps. All the best

[@jasunikanaparthi96]

thankyou

[@MSurveyPoint]

Thanks for your feedback. Happy to know this helps.

[@statistikochspss-hjalpen8335]

Can you elaborate why we change FALSE to null? What happens if we select to unpivot all columns?

[@MSurveyPoint]

@Statistik och SPSS-hjälpen Instead of blanks or spaces (for unselected response items) like in one of the previous videos, some platforms code that as FALSE. So technically, it is indirectly a configuration of "not selected" If you proceed with the FALSE (without replacing it with null): 1. Power query will recognize that as "true" data points. 2. And when you unpivot, the number of rows will move up to 300 rows instead of only 74 rows (number of selected items - coded TRUE). What it means is that each respondent selected all the 6 response items (50*6=300). And this isn't correct. 3. Do you really need this extra 200+ rows? And not to mention its impact on the model size & computing time. 4. Lastly, your pivot table summary will not make sense to you. Count & "distinct count" of IDs f for each response item will be equal to the number of respondents (50) . 5. A further step will be needed to achieve what we had by replacing FALSE with null (To do this: Select "Add a conditional column" then IF Value=Yes, return "Q5. Devices they own"). A null response item will be added to your pivot table summary and needs to be filtered out. Which of the two approaches is optimal & efficient? 6. Try to avoid your request, especially for large datasets. This increases the size of your model and steps in execution (anytime you filter any pivot table coming from the model, this extra step has to be executed before you get your results). Try this to get a practical sense of the two approaches.