Create a pivot table with Year in the column area, and sales in the data
area.
In the Pivot Table Wizard, add another copy of the Sales field to the
data area.
Double-click on the second Sales button
Click the Options button
From the Show Data As dropdown, choose Difference From
As the BaseField, select SalesYear
In the Base item list, select (previous)
Click OK

Add a third copy of the Sales field to the data area.
Double-click on the third Sales button
Click the Options button
From the Show Data As dropdown, choose % Difference From
As the BaseField, select SalesYear
In the Base item list, select (previous)
Click OK, click OK, click Finish


CraigS wrote:
I have a sales application that contains tables of approx 70 fields with
sales data for each year, one of which is a year field. I'd love to combine
the tables into a single table so that I can represent the data in a Pivot
Table, but need to figure out how to show, create, calculate a variance
field, and a % variance field in a pivot table if I do combine the tables.
In the Pivot table I'd want to show:
2004 Sales 2005 Sales Variance % Variance
10 12 2 20%

Note: The Sales field is the same field, just a different year

Any Suggestions?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html