# Thread: Pivot table problem (2000)

1. ## 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. ## Re: Pivot table problem (2000)

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

Steve

3. ## 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. ## 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. ## 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. ## 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. ## 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
•