Results 1 to 3 of 3

Calculating percent of one field over the sum of another

Threaded View

  1. #1
    Registered User
    Join Date
    12-02-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    32

    Calculating percent of one field over the sum of another

    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.
    .
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1