Pivot Table Summary Error?

[Brad Autry]

Greetings,

Sorry, wasn't sure which subforum to post this in, as there's nothing
dedicated to pivot table.

My dilemma is as follows:

I have a spreadsheet where data is entered daily. For my example, just
imagine a spreadsheet with three columns: date, date, delay. Two date
columns are used for the pivot table.

The pivot table is displayed with month and weekly ranges as the row
headers. There is a field in the pivot table that is summarized as a MAX of
one of the source data columns.

Now, the problem I am coming across is the monthly summary MAX is not
calculating properly. Pasted below is the pivot for the January test data.
You'll see that in the first week, the MAX for that week is 31:54. But in
the monthly summary, the MAX figure is showing 23:48.

Max of Delay
Month Date Total
Jan 1/1/2010 - 1/7/2010 0:31:54
1/8/2010 - 1/14/2010 0:26:41
1/15/2010 - 1/21/2010 0:24:44
1/22/2010 - 1/28/2010 0:23:48
1/29/2010 - 1/30/2010 0:03:03
Jan Sum 4:10:47
Jan Average 0:11:57
Jan Max 0:23:48
Jan Min 0:03:03
Grand Total 0:23:48


Why is the monthly MAX figure not 31:54? Am I overlooking something obvious?

Thanks in advance for any suggestions.

Brad

 

[Brad Autry]

As a follow up, I tried to break it down to simpler terms to find the root.
It's still giving me an error even when I break it down to two columns. So,
feel free to replicate for yourself.

Source Data:
Column A is date.
Column B is a time measurement, in xx:xx:xx format.

For my example, I did January 1 through January 31, 2010. There is no data
entered on Saturdays or Sundays. My data is as follows:

Date Delay
1/1/2010 0:08:54
1/2/2010
1/3/2010
1/4/2010 0:11:54
1/5/2010 0:11:29
1/6/2010 0:06:59
1/7/2010 0:31:54
1/8/2010 0:26:41
1/9/2010
1/10/2010
1/11/2010 0:03:20
1/12/2010 0:02:24
1/13/2010 0:15:26
1/14/2010 0:08:48
1/15/2010 0:03:02
1/16/2010
1/17/2010
1/18/2010 0:24:44
1/19/2010 0:01:42
1/20/2010 0:12:01
1/21/2010 0:01:14
1/22/2010 0:17:21
1/23/2010
1/24/2010
1/25/2010 0:20:00
1/26/2010 0:06:02
1/27/2010 0:23:48
1/28/2010 0:10:01
1/29/2010 0:03:03
1/30/2010
1/31/2010

The Pivot Table is simply Date as the Row Label, Max of Delay as the Values.

For the summary row, I wanted a MAX of the month. Using this data, the
summary field is showing 0:23:48. Because there are blanks in the data, the
pivot is not picking up on the higher values. Why this is, I've no idea.

Is there a way to get around this? Entering 00:00:00 in Saturday and Sunday
would cause the MIN values for the actual pivot table to not be accurate.
Removing the Saturday and Sunday rows from the main source data is not an
option.

 

[Daryl S]

Brad -

I copy/pasted your data into Excel and created a pivot table using max
Delay, and I get the correct Grand Total line of 0:31:54...

 

[Brad Autry]

What version of Excel are you using?

Perhaps this is an Excel 2007 issue? I copied and pasted the data I'd
listed yet again into a clean worksheet and it's (again) giving me a wrong
MAX.

 

[Brad Autry]

I had someone in my office who was still running Office 2003 try this. They
get the correct figures. Excel 2007 gives the results I've been complaining
about.

If she sends me the pivot table that she created/saved in Excel 2003, I can
open it in Excel and it's what I want. Is there a way to create a pivot in
Excel 2007 that emulates whatever it is the older version of Excel did for
pivots?

 

[Herbert Seidenberg]

Excel 2007 PivotTable
I agree with Daryl,
no problem found.
Upload your file to
http://www.mediafire.com
Free.

 

[Daryl S]

Brad -

Yes, I am on Excel 2003. Maybe someone else can test this with Excel 2007?