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?


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.

  • It's not a matter of my eyes or your eyes, the average will be different in most cases. The point I'm making is that showing data in this manner of a hierarchical tree implies (IMHO) multiple levels of aggregation, but that is not happening. I am doing more research and may rephrase the question in terms of OLAP cubes. – Patrick Mar 7 '11 at 14:09

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.