Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Insert Time Calculation into Pivot Table and Cell Format for Time Incl Days (XL07)

    I would like to insert a calculation in a pivot table that divides the results of one field by the count of another field. In the attached workbook I have added this formula Avg Dur/ Trans2 ='Duration Sec'/TransID, but the results are not what I am expecting when compared to a manual. In a second formula Avg Dur/ Trans2, I added COUNT and COUNTA functions to the formula but that didn't help. How can I get the correct formula into the pivot table so that it will maintain the calculation?

    A second problem, but still related, I cannot figure out how to get a cell format that properly displays the cumulative time in this pivot table. The first item in the pivot table, a monthly total 8,581,421 seconds, should calculate to 99+ days. But the formats I have tried don't even come close.
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    On your second point, Excel's base unit of time is one DAY. So formatting alone cannot convert seconds into days - first you have to divide the seconds by 86,400 (the number of seconds in a day) then you can format it to show the correct number of days, hours, whatever. This calculation correctly converts your 8,581,421 seconds to 99.322 days.
    HTH

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Calculated fields in a pivot table always SUM - you can't use a count. If you have PowerPivot, it's a pretty simple measure, but if not, you'll have to add a column to the source data that returns 1 for each record so that you can sum that instead of counting.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Got it. I've done that before. I was just hoping that I had been missing a function/formula shortcut. Thanks Rory.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Here's my follow-up:
    I went back to my source data, a Crystal query that addresses one of our businesses's primary databases. In the query I added two fields (both calculations) so that I wouldn't have to modify a spreadsheet each month. One field divides the TransID by itself to generate a transaction counter. The other new field I call Duration (DayFraction) which divides the system's duration maintained as seconds by 86400). Then in a pivot table I insert a formula ='Duration (DayFraction)'/'Transaction Count', which when properly formatted displays the
    time in the pivot table work as expected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •