Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot table problem (2000)

    I have a pivot table that has Renewal Date and District as the rows. Status(Declined, Pending, Prospect, Enrolled,) defined as columns. There is a column outside the pivot table for UBHP Renewals. It breaks on Renewal Date giving me I total members for each renewal month broken down by district. That aside There is a small report under the pivot table where they have the following instructions:
    Enrolled is the same as Total Enrolled (pivot table)
    "Projected Low" is calculated as the sum of "Enrolled" and "UBHP Renewals" multiplied by 0.5 (this is the part where I am stumped) where the renewal data is greater than the report date.

    The renewal dates range from 7/1/05 to 1/1/06. I am assuming that since todays date is Sepember 16, 2005, they only want the totals to reflect only Renewal dates from 10/1/05 on. How would I do this without doing it manually? If this is at all possible.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot table problem (2000)

    Could you create the pivot table
    add an autofilter
    then use the autofilter to filter the dates?

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table problem (2000)

    Thank you - that is a good idea.

    How would you formulate this:
    "Projected Low" is calculated as the sum of "Enrolled" and "UBHP Renewals" multiplied by 0.5

    I would do :
    (Enrolled + UBHP Renewals)*0.5

    The way they are doing it is - Enrolled + UBHP * 0.5

    The formulas give different results. Which one is correct?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot table problem (2000)

    If you want one-half of the sum of the 2 values:
    (Enrolled + UBHP Renewals)*0.5

    If you want the enrolled + one-half of (only) UBHP then:
    Enrolled + UBHP Renewals*0.5

    Excel (unlike many "cheaper" calculators) knows about "precedence": Multiplication is done before addition.

    The equation:
    Enrolled + UBHP Renewals*0.5

    is equivalent to:
    Enrolled + (UBHP Renewals*0.5)

    or
    UBHP Renewals*0.5 + Enrolled

    Steve

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Location
    Ontario, Canada
    Posts
    57
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot table problem (2000)

    Looks like they are trying to get a low estimate on projected enrollees. If that is so, then their version of the formula is correct. The "already enrolled" numbers are fixed, and they are projecting that 50% of the UBHP Renewals will ultimately enrol.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot table problem (2000)

    If that were the case I would use:
    "Projected" = Enrolled + UBHP Renewals*0.5
    "Projected Low" = Enrolled
    "Projected High" = Enrolled + UBHP Renewals

    The "low" would assume no new ones added the "high" all the renewals. As for the "0.5" I would use a number that was "typical of past performance" as my estimate.

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot table problem (2000)

    Thanks, I did figure it out but the way I was reading it was wrong. I used Enrolled + (UBHP Renewals *0.25) just to be on the safe side. They have "Projected Medium" and "Projected High" too with 0.5 and 0.75 respectively.

Posting Permissions

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