Percentage of total for binary data

[s100]

Hello,

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

Col1	Col2	Col3
Yes	Yes	No
Yes	Yes	No
No	Yes	No
No	Yes	No
Yes	Yes	No
Yes	Yes	No
Yes	Yes	No
Yes	Yes	No
No	Yes	No
Yes	Yes	No

The second one is the output table that I need.

	Yes	No	Total	Score
Col1	7	3	10	0.7
Col2	10	0	10	1
Col3	0	10	10	0

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 solution]

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 solution]

@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 solution]

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 🙂

[s100]

@AngelosPachis @atcodedog05 Thank you so much for your help! These solutions work.