How to insert a calculated field within the data section?

[poiu]

Hi,

I need to add a calculated field into the data area of a pivot table, please could you tell me how to do this?

For example, if I have the following source data:

Period, Cost Centre, GL Account, Amount
P1-P10, CC1000, 600000, 10000
P1-P10, CC1000, 700000, 20000
P12, CC1000, 600000, 1000
P12, CC1000, 700000, 2000
P1-P12, CC1000, 600000, 12000
P1-P12, CC1000, 700000, 24000


I would like to output the following pivot table (with a calculated field for P11):

Cost Centre, GL Account, P1-P10, P11 (Calculated Field), P12, P1-P12
CC1000, 600000, 10000, 1000, 1000, 12000
CC1000, 700000, 20000, 2000, 2000, 24000


My problem is that I think a calculated field has to be calculated on the field names of the source data, but I am looking to calculate on categories within a field (i.e. period ranges within the "Period" field)?

Thanks,

Poiu

 

[Andrew Poulsom]

Select the Period field and insert a Calculated Item with the formula:

='P1-P12' -P12 -'P1-P10'

Result:

Sum of Amount		Period
Cost Centre	GL Account	P12	P1-P10	P11	P1-P12	Grand Total
CC1000	600000	1000	10000	4000	15000	30000
	700000	2000	20000	2000	24000	48000
CC1000 Total	3000	30000	6000	39000	78000
Grand Total	3000	30000	6000	39000	78000

<tbody>
</tbody><colgroup><col><col><col span="4"><col></colgroup>

 

[poiu]

Thank you Andrew, that's great.

When I insert a calculated item all I see is the heading "Formula1" - there is no popup window that allows me to enter a formula (which should happend according to this - Calculated Items in Pivot Tables « Guj800).

Please could you tell me how to enter a formula when all I can see is "Formula1" without a popup box?

Thank you,

Poiu

 

[Andrew Poulsom]

Do you mean that you don't see the 'Insert Calculated Item' dialog? Which version of Excel are you using?

 

[poiu]

Yes - I'm using Excel 2003. I've figured it out - Insert->Insert item doesn't bring up the popup, but if I go to the pivot table toolbar->Formulas->insert item then it does work.

Thanks