PDA

View Full Version : Excel Pivot Table Failing to Calculate an Average on a Calculated Field



NukedWhale
02-05-2010, 04:18 PM
Hello,

Please see the attached worksheet for an example of the error. There is a worksheet with source data and a worksheet with the pivot table.

My field is set to calculate averages, but it's clearly calculating sums. Any ideas? Am I totally missing something?

Paul_Hossler
02-06-2010, 08:04 AM
1. Your data has 2007 in twice.
2. Average is the average for the row (=Year)

So if I make the second 2007 into 2010, the average on the 2007 line = 5, or (10 - 5) / 1 since there is only 1 value being averaged

if you want the average of the yearly averages, I'd put a =AVERAGE( ) into the sheet

Paul

NukedWhale
02-08-2010, 10:22 AM
I've attached a better example of my error.

I have added a field in the database that's calculated on actual-budget
I have added a calculated field to the pivot table that's calculated on actual-budget

Both fields are added to the pivot table and are set to calculate averages. However, I'm getting two different answers. Any ideas as to why?

Is this a known Excel bug?

NukedWhale
02-09-2010, 03:03 PM
Bump. Sorry to be a bother. :/

Aussiebear
02-09-2010, 07:00 PM
Have a look at your data. As Paul kindly pointed out you have 2007 data in twice in the data set.