Pivot Table

Sanchit297 · #1 11-21-2009, 01:14 AM

Hi,

I am having some trouble with using Pivot Table. I have attached the excel sheet below.

In my case, i would be taking data from SQL tables and exporting to Excel "RawData" sheet. From this sheet i have designed a Pivot Table.

Code:

Location
BranchID
Total Count
ScanDateTime
DataDateTime
Longest Elapsed Time (DataDateTime - ScanDateTime)
Average Elapsed Time (Average of all Longest Elapsed Time)

Huron · #2 11-21-2009, 11:31 AM

There are problems with your RawData sheet. The Longest Elapsed Time is formatted as actual time (hh:mm:ss) but it should be formatted as elapsed time ([h]:mm:ss). You can see the problem in your sheet on times which are longer than 24 hours eg line 25 where ScanDateTime is 22 Oct and DataDateTime is 24 Oct but your formatting shows this as just 20 hours instead of 44 hours.

The pivot table is showing the correct values in elapsed time format.

I'm not sure how you are getting the Average Elapsed Time but it is frequently greater than the Longest Elapsed Time which can't be possible.

Sanchit297 · #3 11-22-2009, 07:53 AM

Thanks Huron - Ive corrected my sheet.

Now, i do not want to Show "ScanDateTime and DataDateTime" in my Summary Rows after each Location and Grand Total - How should i proceed here ??

In the Grand Total row at the end i want the "Longest Elapsed Time" to be the Longest time from the individual "Summary rows" calculated for each location. How should i proceed here ??

I have attached the updated Sheet.

Sanchit.

Sanchit297 · #4 11-22-2009, 12:04 PM

I would appreciate a quick response from anyone on the issue im facing.

Sanchit.

Huron · #5 11-22-2009, 02:48 PM

I don't think you can have summary rows for some data fields but not others - as far as I know it's all or none.

The Grand Total at the end is showing the Longest Elapsed Time ie 118:30:08 which was the value for Australia.

Welcome to the Excel Forum

Forum Statistics