Pivot running total %

  • Thread starter Thread starter mcquam
  • Start date Start date
M

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.
 
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
 
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.
 
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.
 
Back
Top