I am trying to use multiple response data in a pivot table.
A survey was given to students asking them to check the colors they like. They could select as many as they wanted among Green, Blue, Red.
For example:
- 7 out of 7 (100%) females in School ABC selected Green.
- 5 out of 7 (71.4%) females in School ABC selected Blue.
- 2 out of 7 (28.6%) females in School ABC selected Red.
- similar data for the males in the school, and for students from other schools.
The data I have is aggregated like so:
School Gender StudentCount Color ColorCount
ABC Female 7 Green 7
ABC Female 7 Blue 5
ABC Female 7 Red 2
ABC Male 8 Green 8
ABC Male 8 Blue 5
ABC Male 8 Red 3
etc.
In the pivot table I can't figure out how to calculate the percents (7/7 = 100%, 5/7 = 71.4%), etc.
My Pivot table has StudentCount and ColorCount in the data area. My hope was to change the options for ColorCount to show data as the "% of" and select the StudentCount variable, but that's not an option.
Any suggestions? The data with pivot table is attached.
.
Bookmarks