+ Reply to Thread
Results 1 to 1 of 1

Excel 2007 : Calculating conditional and cumulative numbers and percents in a pivot table

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

    Calculating conditional and cumulative numbers and percents in a pivot table

    (Sample data with pivot table is attached.)

    I have raw data arranged like below. The Count field in each row shows the number of prisoners who paroled or died, by State, Year, Gender, and the Year Interval when it occurred (0-1 yrs, 2-4yrs, 5-6 yrs). (I actually have several more variables, like Race, etc. but am omitting them here for simplicity.)

    Please Login or Register  to view this content.
    For example, in Alabama in 2006 50 male prisoners were Paroled during their 1st yr (0-1). 5 died during their 1st yr, etc. Notice that no males died during the 2-4 year interval, so there isn't a row for that. Also, for 2007 (not shown), there won't be rows for the 5-6 interval because it hasn't finished yet (need to wait for 2013 for that data).

    I can pivot the data like so, using the Sum of Count for the cells:

    Please Login or Register  to view this content.
    But I'd also like to calculate these summary statistics, flexibly enough so that if I restructure the pivot table the statistics get recalculated.

    1. The number entering each interval.

    - For the first (0-1) interval, this is 121 or (70+6+25+2+14+4) (i.e., the Grand Total).
    - For the subsequent intervals, it's the number of prisoners remaining in prison at the end of the previous interval. For example, the number entering the 2-4 interval = 45 which is 121-(70+6). The number entering the 5-6 interval = 18 which is 45-(25+2).

    2. The % paroled per interval.

    - The number paroled during that interval / the number entering that interval

    3. The cumulative % paroled, by interval.

    - The cumulative number paroled / the number entering in the first year

    I've worked at this for a while and can't get these statistics (I tried calculated fields but couldn't figure out the formulas). I'm also willing to use helper columns, but they quickly fell short because I may need to show the data by certain States, certain Year(s), certain age groups, etc.

    Any suggestions? Thank you.
    Attached Files Attached Files

+ Reply to Thread

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