Pivot running total %

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.
 
M

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
 
M

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.
 
M

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vlookup or Match 6
Find Period Date? 5
Pls help for Simple Calculation 1
Inconsistent excel 2007b2 time calculations that span whole days 2
Find within Date Range 4
Need to pull <=14 Days, <=30 Days, >30 Days from a date column 3
Grouping Time in Pivots. 2
Excel 2002: How to fill up data in running order? 3

Top