Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot running total %
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot running total %
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot running total %
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot running total %
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot running total %
Changed the DaysPctC formula to simulate
the PercentRank() function. http://www.savefile.com/files/1618459 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot running total %
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot running total %
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot running total %
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)*SLASLA 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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot running total %
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running Total in Pivot Table | Excel Discussion (Misc queries) | |||
Pivot table and running total | Excel Discussion (Misc queries) | |||
Pivot table and running total | Excel Discussion (Misc queries) | |||
% of running total in pivot table | Excel Discussion (Misc queries) | |||
Pivot Table - Running Total Annualised | Excel Discussion (Misc queries) |