Pivot Table Summarizing Quirk - Please Help


Brad Autry

I am using Excel 2007.

I have a spreadsheet that has about 20 columns of data for each day of the
week in a calendar year. I create a pivot table that summarizes this data,
broken down by Month, and then by Week.

There is no data for Saturdays and Sundays, but the empty rows must remain
in the source data.

Now, my problem:

When I create the pivot table, I summarize data by month with Average, MAX,
MIN, and Sums.

Because there are blanks in the data, the MAX and MIN functions are only
looking at the final week of the month. Why is this? How can I get around
it? Entering 00:00:00 is not an option, as the MIN data would error.

To replicate the problem if you are so inclined, you could just make a
spreadsheet with just two columns, like this:

1/1/2010 0:08:54
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/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/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/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

Date as the Row Label. Max of Delay as the Values. The MAX value in the
overall summary shows 00:23:48.

Please help me!

Many thanks in advance,

Brad Autry

As a follow-up to my own post here, I'd just like to point out that the SUM
and Average summary figures take into account the whole month. Only the MAX
and MIN values do not show properly, as they only take into account the final

If I were to enter data into the Sat and Sun blanks, then MAX and MIN work
properly for the month. But what values could I enter that would not mess up
the other figures? There are none that I can think of.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question