Pivot running total %

[mcquam]

I have the following data:

date days
06/06/08 12
11/06/08 8
13/06/08 21
16/06/08 5
17/06/08 18

I need to show this as a pivot table

month ave. -top20% bottom80% total

May xxx xxx xxxx
Jun 21 10.75 12.8

There are 5 entries so 21 is excluded from the bottom 80% as it represents
the highest 20% of cases.

I am using Excel 2007 and it looks as if I should be able to do it. I can
do it without any grouping but i get wrong results when i group.

 

[Herbert Seidenberg]

Pivot Table with helper columns
and variable %:
http://www.savefile.com/files/1617630

 

[mcquam]

Many thanks for your trouble. Please see "myversion" under your method#2
which shows my expected results. i have added 2 columns to the table to show
how my calculation should work.

I think the key to this is the DaysPctC but your assumption that all dates
are unique is wrong.

Is it not possible to use the days field as a running total and group the
columns according to their share of the total which would not require the
DaysPctC calc?

http://www.savefile.com/files/1617955

 

[mcquam]

Correction: there are no duplicate dates, but there are multiple records
showing a number of days for each date. So, the average is based on all
records for the month regardless of the day they occurred.

 

[Herbert Seidenberg]

Changed the DaysPctC formula to simulate
the PercentRank() function.
http://www.savefile.com/files/1618459

 

[mcquam]

Many thanks again. Your solution looks perfect but I can't replicate it.
Please see my sample data. No doubt I am missing something.

http://www.savefile.com/files/1620070

 

[Herbert Seidenberg]

Some restrictions apply:
http://www.savefile.com/files/1620974

 

[mcquam]

Many thanks again for your formulas and explanations. I have a question:
Using your formulas there are 37 cases out of target for May. If I change
your Pct formula to (MonthD=MonthD 2:2)*SLA>SLA 2:2) rather than
(MonthD=MonthD 2:2)*SLA>=SLA 2:2) to make target 80 inclusive (I presume), I
get 46 results out of target. This should be 43 if it represents 20% of the
216 records. The results are 7.08 and 7.24.

 

[Herbert Seidenberg]

Account for SLA RANK duplicate
http://www.savefile.com/files/1625729