Difference In An Excel Pivot Table

[ddavis]

I am trying to create a difference column in an Excel Spreadsheet that
contains a Pivot Table. The Table is based on data marked in a
spreadsheet identifying each record as pertaining to a policy year of
2005/2006 or 2004/2005. I want to get the difference between the
subtotal for each of these years. I have tried using the formula
option on the pivot table wizard menu bar. However, when I try to pick
the fields only one field is listed which is named "Year" not one for
each individual policy year. I am attaching the spreadsheet in hopes
that someone can help. I can easily do a manual calculation in the
next column by doing a difference formula but I am really wanting this
to be part of the pivot table. Any assistance is much appreciated. I
do a lot of pivot tables and have never been able to figure this out so
I thought I would ask for some assistance.


+-------------------------------------------------------------------+
|Filename: February2006PackagePolicyAP&GLDetail.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4543 |
+-------------------------------------------------------------------+

 

[Debra Dalgleish]

You can create a calculated item:

Select the Year field button in the pivot table.
On the Pivot toolbar, choose PivotTable>Formulas>Calculated Item
Type a name for the formula
In the formula box, create a formula to subtract the years, e.g.:

='2005/2006'- '2004/2005'

Click OK

 

[ddavis]

I did as you suggested but I cannot get it to work. I suspect I am
missing something because when I try this the fields '2005/2006' and
'2004/2005' do not come up. Instead in the box to insert fields it is
'Year' that comes up.

Thanks for trying

 

[Debra Dalgleish]

Did you select 'Calculated Item' from the Formula command, or
'Calculated Field'?

 

[Guest]

Debra

I've tried exactly what you said and I get an error message stating the item
cannot be modified. The max number of items allowed in a field my have beenr
eached, macro incorrect or not enough memory.

So I saved my file to my hard drive and still get the same error.

I would really love for this feature to work. Any suggestions?

Misty

You can create a calculated item:

Select the Year field button in the pivot table.
On the Pivot toolbar, choose PivotTable>Formulas>Calculated Item
Type a name for the formula
In the formula box, create a formula to subtract the years, e.g.:

='2005/2006'- '2004/2005'

Click OK