Pivot Table will not sum filtered positive and negative time values together

[sonofearth]

Dear Friends

I'm in desperate need for help , as I have to deliver a report in the coming two days

I work in healthcare management and I prepared an excel sheet for the time of attendance of physicians compared to the scheduled clinic start time , then I used pivot table to segregate each type of scheduled time alone ( clinics have 4 scheduling times 9, 9:30, 17:00, 16:30) ... to calculate the average delay time compared to each type of scheduled starting time, I reviewed the formatting of the cells and everything , I also used the 1904 timing option so I can calculate negative values too , as sometime the physician will start earlier than the scheduled time, so here is the thing ... when pivot table extracts the data , they are accurate , but when it adds them together it comes up with a larger number as shown in the pivot table page in the attached excel sheet , the cells shows accurate numbers but the subtotals are extremely large numbers compared to the cells ...... and the weird thing is ..... this happen for certain clinic starting time , while the other clinic times appear accurate and calculate accurately as subtotals too ... Help plz

I also noticed that the problem is when there are both positive and negative values ......... pivot table has a problem calculating averages from positive and negative time values .... and when I manipulated the data so that no result will be negative , the calculation turns out fine...

[canapone]

Hi,

a good strategy could be to add the averages you'd need in the source data.
As far as I know the "Average" calculation has to be done outside the pivot table, there seems to be no way the pivot will give averages for rows.

I'm a Excel 2000 user, for Excel 2010 you can download Powerpivot add for free: maybe Powerpivot could give you better outputs.

Regards

[shekhar1660]

If you use sum rather then Avg, it will give you correct picture.
Also change the calculation of "After Hours time spent" as =D2-F2

[sonofearth]

Thanks CANAPONE and Shekhar1660, So that Means that Pivot tables cannot calculate averages for filtered data that contains both positive and negative values ??

[jpr73]

Hi,
See attached the result with the right format
Instead of hours format, use custom format [hh:mm]
You can also check the right result in the table by using sub-total average and filter clinic start column
Hope this helps
Best regards

[FDibbins]

just to respond to canapone, 2007 PT's do allow for averages in the PT, right-click on 1 of the cells in that column, select "value field settings" and select average. I tested it with zeros and negative numbers and it seems to work fine.

[sonofearth]

Oh Thanks all for your contribution , Especially jpr73 ....... and actually when I downloaded your attached sheet it looked fine , but as soon as I filtered a result in the same table or clicked on the format cell option to see what kind of format you were using ........ it instantly changed and returned back to the same errors .... and even values are still the same ...... IT REALLY AMAZES ME ..... This is very very very WEIRD.... so as soon as I click on the "Value Field Setting" in the table , go on and check the format for example ...then i get out of it , the values return errornous again , even though I did not change anything ...