How do I display both a sum total and an average in pivot table?

[Guest]

Hi there,
I'm trying to construct a pivot table where I'd like to display both the sum
total for a column, AND THEN the average directly below it.

I can do it by dragging the field into the data area a second time, but then
I'm listing duplicate amounts for all but the total (which is a sum total in
one column and an average in the next). I'd like to avoid that replication if
possible.

Someone told me I could do it using Subtotals ... but that option is grayed
out.

Thanks for any advice you can provide!

 

[Guest]

Catarina,
Yes, you can use Subtotals, but you don't add them to the pivot table, you
need to go back to the list. If the Subtotals option is still greyed out, and
you're using Excel 2003, you need to convert your list back to a range.

In the list, Sort on the column that you want to subtotal the list by, then
Data|Subtotals. Choose the column that you just sorted by in the 'At each
change in', then choose the sum function and put ticks in the required 'Add
subtotals to' columns. Click OK. Then do the same again for the average, but
before you click OK, remove the tick from the 'Replace current subtotals' box.

 

[Guest]

Hi MarkN,

Thank you for this reponse. I could indeed add the two functions to the
original worksheet. When I tried to bring this calculated information into
the pivot table, however, I got an error: "Because your source data has
automatic subtotals, Microsoft Office Excel cannot create a Pivot Table
report from it. For information about how to remove the subtotals, click
Help."

So maybe I misunderstood ... when you wrote, "you don't add them to the
pivot table, you need to go back to the list" were you in fact saying there's
no way to display this information in a pivot table?

Thanks again!

 

[Guest]

Hi Catarina,
Firstly, a pivot table summarises a list, inserting automatic subtotals
destroys the structure of the list so if you've applied subtotals, you can't
create a pivot table.

Secondly, you can display the sum and average in a pivot table with no
duplicated data being displayed:
-Drag the same field into (the data items area of) the pivot table twice.
You will have two 'sum' fields.
-The field button will be labelled 'Data' and you'll have 'Sum of xxx' and
'Sum of xxx2'. Righ-click over one of these (not the Data label) and choose
Field Settings...
-Click on Average and press OK.

I think this is what you want but post back if you need anything else.