Results 1 to 7 of 7
Thread: Pivot table problem (2000)

20050916, 15:05 #1
 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.

20050916, 15:26 #2
 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

20050916, 16:56 #3
 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?

20050916, 17:23 #4
 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 onehalf of the sum of the 2 values:
(Enrolled + UBHP Renewals)*0.5
If you want the enrolled + onehalf 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

20050916, 17:24 #5
 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.

20050916, 17:43 #6
 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

20050916, 18:15 #7
 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.