B
Brad Autry
I'll try to summarize as best as possible:
I use Excel 2007. I've pasted in data below that is a part of a larger
spreadsheet that I am analyzing via pivot table. I would like MAX as one of
the summary fields for the month. In Excel 2007, the MAX and MIN summary
fields do not work properly. Well, perhaps it is by design, but I can't
imagine it being that way. If there are blank cells in the source data, as
is the case here, the summary fields only work with the most recent grouping
of data. So, with the data below as our example, the MAX field gives 0:23:48
instead of the proper answer of 0:31:54.
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
In prior versions of Excel, if you pasted in this data and created a pivot
and used MAX in a summary field, it yields the correct figure. I had a
coworker who was working with Excel 2003 test this out - it gave 0:31:54. I
had her save the worksheet, send it to me... I open it in 2007 and it has the
right answer. Mind boggling, this is.
Filling in the blank saturday and sunday cells with 0s or something like
that is not an option (as I require MIN in my summary also). Using something
other than Excel 2007 is not an option. Asking my coworker to create the
large scale pivot table I am working on is not an option.
My question:
Is it possible to emulate an older pivot table engine (or whatever you would
call it) in Excel 2007? Or any other idea, for that matter, to help me
resolve this...
Thank you for reading this, and thanks ahead of time should you have any
suggestions.
Brad
I use Excel 2007. I've pasted in data below that is a part of a larger
spreadsheet that I am analyzing via pivot table. I would like MAX as one of
the summary fields for the month. In Excel 2007, the MAX and MIN summary
fields do not work properly. Well, perhaps it is by design, but I can't
imagine it being that way. If there are blank cells in the source data, as
is the case here, the summary fields only work with the most recent grouping
of data. So, with the data below as our example, the MAX field gives 0:23:48
instead of the proper answer of 0:31:54.
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
In prior versions of Excel, if you pasted in this data and created a pivot
and used MAX in a summary field, it yields the correct figure. I had a
coworker who was working with Excel 2003 test this out - it gave 0:31:54. I
had her save the worksheet, send it to me... I open it in 2007 and it has the
right answer. Mind boggling, this is.
Filling in the blank saturday and sunday cells with 0s or something like
that is not an option (as I require MIN in my summary also). Using something
other than Excel 2007 is not an option. Asking my coworker to create the
large scale pivot table I am working on is not an option.
My question:
Is it possible to emulate an older pivot table engine (or whatever you would
call it) in Excel 2007? Or any other idea, for that matter, to help me
resolve this...
Thank you for reading this, and thanks ahead of time should you have any
suggestions.
Brad