Percentage of total for binary data

Question

Hello,

This first table is my original data which contains several columns with binary data 'Yes' or/and 'No'.

Col1Col2Col3YesYesNoYesYesNoNoYesNoNoYesNoYesYesNoYesYesNoYesYesNoYesYesNoNoYesNoYesYesNo

The second one is the output table that I need.

YesNoTotalScoreCol173100.7Col2100101Col3010100

I tried Summarize tool to get the count of 'Yes' and 'No' for each column. However, if I have 20 columns, then 20 summarized tools need to be used which I think is too complex.

Please help me if you know a better way. Any help is appreciated!

AngelosPachis · Accepted Answer

Hey @s100 ,

One way would be to transpose your data so you only need a single summarize tool to count the Yes/Nos per column.

Then you only need a formula tool to find the score.

Hope that helps,

Angelos

AngelosPachis · Accepted Answer

@s100  A neater solution, use a Frequency Table tool from the Data Investigation tool palette.

Same results more or less, but only a single tool used and it will automatically return the Score value

atcodedog05 · Accepted Answer

Hi @s100

Here is how you can do it.

Workflow:

1. Using transpose tool converting column name to rows.

2. Using formula tool create count column.

3. Using crosstab converting rows to table with column with yes, no & total.

4. Using formula tool calculating score.

Hope this helps 🙂