Total of 2 columns in pivot table

[Guest]

Hi

I have used a pivot table to work out and display the average/max of a set
of data twice for 2 different things so i have four columns in the data part
with two showing the average and another showing the max for an occurence and
and two other columns doing the same for a differnt occurence. I would like
to be able to add the two Average columns to have a total average and the two
Max columns to have a total max.
I got this to work by putting using the cells outside of the pivot table and
simply adding the cells. But is there a way of doing it so it is actually
part of the pivot table?

It looks a little like this


Ave 140 Max 140 Ave Multi Max Multi
23 25 20 20
25 25 20 20


Many thanks

 

[Guest]

Create a calculated field.

In Excel 2007: click in a cell in you PivotTable, under PivotTable Tools |
Options | Formulas select Calclulated Field...

Input a Name for the Calculated Field in the Name text box (the name will
show up as a field in the Field List).

In the Formula text box input your formula. For example, if you want the
average of a field named Orders, input:

= AVERAGE(
then select the field name from the Fields: list, click the Insert Field
button.
then close the brace in the formula, e.g., =AVERAGE('Orders')
then click OK.

the new Calculated Field should now appear in the Field List. You can now go
back and add more calculated fields that take aveages or averages etc. as you
want to do (i.e., create the calculated field first, then you can use it in
another and new calculated field). Of course you know PivotTables have the
MAX, MIN and AVERAGE functions built-in as well (right mouse click on a field
in the PivotTable, select Field Settings and specify a Custom setting i.e.,
MAX, MIN, AVERAGE etc. OR right mouse click on a field in the PivotTable and
select as setting from the Summarize Data By... list).

 

[Guest]

Hi Thanks for your answer

I understand how it works so that is good. But i think i left out a vitl
piece of information so i can't get it to work.

The table looks like this

Round Day Ave 140 Max 140 Ave Multi Max Multi
W1 mon 23 25 20 20
W2 Tues 25 25 20 20

So the information is against rounds and then data.

I used the Ave and Max functions in my pivot table which worked i just need
to be able to add the Ave 140 and Ave Multi so i have a total Ave for the day
against the round.

Thanks again

 

[Guest]

Just a quick note

I have tried the calcualted field and it works against the rounds and days
but on in Sum format . so if i put =AVERAGE(140) it returns the sum cottectly
against the round and name. I also tried MAx which returned SUM as well.
Could this be i am using 2003 not 2007

Thanks for your help

 

[Philip J Smith]

Hi.

I can't see "Pivot Table Tools". Can you tell me where I can find it please?

Regards

Phil

 

[Roger Govier]

Hi Phil
In 2007, when you click on the PT, 2 new tabs appear under Pivot Table
Tools - Options and Design.
Click the Options tab.
This tab is divided into sections. In the Tolls section, click on Formulas
and you will get the dropdown allowing you create a Calculated Field.