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

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

Got it. I’ve done that before. I was just hoping that I had been missing a function/formula shortcut. Thanks Rory.

Reply | Quote

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.

Reply | Quote