Excel 2007 pivot table does not aggregate properly

Question

I am using a an excel pivot table to summarize some data and just found a problem. The problem deals with how aggregate values are calculated. Let's say I have a table of data with three columns: Name, Date, Value. If I create a table where Name and then Date are used as Row Labels and Value is the aggregate value, ie Average. The pivot table will look something like this:

+John             .3450 
   5/14/2010       1.234 
   5/15/2010       3.450 
   5/16/2010       -3.25

What I think should be happening here is that the values for each date are averaged and then those values are averaged to come up with the value in the same row as the Name, John. But that is not what it does. It takes the average for each date, which it shows across from the date, but then instead of taking the average of those numbers, it actually uses the raw data and computes the average for all of John's values. It should show the average of the daily averages to correspond with the tree hierarchy, but instead just shows me the average for all of John's values. It essential will only aggregate at one level, but visually creates sub levels that it is not using. Does anyone know how to change this or understand by what logic this makes sense? Why would I create any sub groupings if I cannot compute aggregates on them?

Answer 1

You've correctly identified what the Pivot table is doing, namely calculating the average of all "John" items, rather than averaging the averages.

I'm sure there's a formal reason for doing this, but in my eyes it means that the overall average is not distorted, for example by cases where you have one outlier observation for a day, but many observations on other days.

Answer 2

If you drag another version of the date field into the DataField, I'm sure you will find that this dataset is more heavily skewed to 5/16/2010.

There is no way to force Excel to create the average based on the level below it, you would have to copy the data to another area in the workbook and calculate the average directly.

This may not be what you want to hear, but it's the only option.