How do I take an average of two individual data items within a field in a Pivot Table in Excel?

February 10, 2005 11:06 AM

Excel Purgatory - Help!

How do I take an average of two individual data items within in a field in a Pivot Table? (More Inside)

My raw data looks like a much expanded version of this:

Company Year Value
Company A 1 10
Company A 2 15
Company A 3 20
Company B 1 25
Company B 2 30
Company B 3 35
Company C 1 40
Company C 2 45
Company C 3 50


And the table I want to end up with is sort of like this:

Sum of Value Year
Company 1 2 3
Company A 10 15 20
Average 12.5 17.5
Company B 25 30 35 Average 27.5 32.5
Company C 40 45 50
Average 42.5 47.5

I don't have a choice in how my raw data comes to me, and I have to do this in a pivot table for a bunch of reasons. The actually pivot table itself is pretty huge, lots of calculated fields and such. I thought I knew what I was doing, but I am totally lost on this. Search the MSFT Knowledge base, a few XLS message boards. Nothing is getting me there.

I can create "calculated Items" that run down the table rather than across but that is pretty useless to me

Any help much appreciated.

(Apologies I can't quite get the formatting right on the table.)

posted by JPD to Computers & Internet (8 answers total)

Why not create a column after the Value column in column D named the Average column, which would have a formula something like:

=IF(B2=1,0,AVERAGE(C1,C2))

(assuming column B=Year, column C=Value)

And then drag it down, let it autocalc that running average between the years if the years aren't "1". Then, make a new pivot table with a layout including column D putting Company in the row field, Year in the column field, and the Sum of Value and Sum of Average values in the data field. That'll create something that doesn't look attractive but is technically what you're asking for with some extraneous summing; you can play around with the formatting from there.

Or am I missing something? Or are you not allowed to autocalc data on the main sheet you're deriving the table from?
posted by eschatfische at 11:50 AM on February 10, 2005

Response by poster: yup no calcs on the main sheet. Its pretty important that I be able to do everything within the body of the of the pivot table itself.
posted by JPD at 12:13 PM on February 10, 2005

Wow. That's beyond my Excel-fu..
posted by eschatfische at 12:54 PM on February 10, 2005

Not sure it's possible within a Pivot table, but definitely possible with a standard formula that you can copy a million times.
posted by grateful at 2:23 PM on February 10, 2005

You might be able to do it with VBA, i.e. create a macro that does the math but doesn't show it on the sheet.

Does the pivot table have to stay a pivot table? i.e. can you copy it and 'paste values' onto itself to make basically the same thing, but not as a pivot table?
posted by bingo at 7:52 PM on February 10, 2005

Response by poster: Yup - underlying data changes regularly so it has to stay a pivot table. The VBA idea is intriguing. I know how to write the macro to create the range but have no idea how to get the pivot table to add that range to the rest of my data. Any thoughts?
posted by JPD at 10:47 PM on February 10, 2005

I wouldn't swear a blood oath on it, but I don't think that this can be done the way you're describing. Why does the change have to take place inside the pivot table?
posted by bingo at 5:34 PM on February 11, 2005

The fact that the data changes regularly may not mean that it has to stay a pivot table. The macro could look at the pivot table, recreate it as a non-pivot table, and then add the info you want to add. If you go into a bit more detail about the reasons for your parameters, I might have more ideas.
posted by bingo at 5:35 PM on February 11, 2005