add a column to a pivot table that would show the difference between 2 other Columns

Hello,
I would like to add a column to a pivot table that would show the
difference between the two other columns.

Below is a sample of data and the pivot table I can get to.

PrjType.........YR...........hrs
1...............2003............5
1...............2004............3
2...............2003............6
2...............2004............8
1...............2003............7
1...............2003............6
1...............2003............5
1...............2003............7


........................................____YR_______
ProjType......Data...................2003.........2004
1.............Sum of hrs...............30...........3
...............Average of hrs2...........6...........3
2.............Sum of hrs............... 6...........8
...............Average of hrs2...........6...........8

What I would like is to add a column ("Diff") to the right, after
"2004" column to show the difference between 2004 and 2003-to
look like

.......................................___YR____
ProjType......Data...................2003.....2004......Diff
1.............Sum of hrs...............30.......3.........-27
................Average of hrs2.........6........3..........-3
2.............Sum of hrs............... 6.......8...........2
...............Average of hrs2..........6........8............2

Thanks

You have a couple of ways of handling this. First you can insert a
calculated item to handle this:-

With your table as you had it, drag the project type into the ROW fields,
the Year into the COLUMN fields, and the Hrs into the DATA field. Now click
on any of the years headings, ie 2003 or 2004, and then with the Pivot table
toolbar showing, click on Pivot Table / Formulas / Calculated item

On the dialog box that appears, replace the 'Formula1' with some logical
title such as 'Delta', and then with Year selected in the box on the left,
in the box where it says ' Formula = 0', delete the 0, double click the
2004, type - and then double click the 2003 such that the formula now looks
like = '2004'- '2003'. Now just hit OK and it will be added.

This works fine most of the time, but depending on how big your Pivot table
is, it may well bring it to it's knees. I use a laptop with a 1.7GHz
Centrino processor and 1GB of RAM. That is a lot of processing power, and
on some of my Pivot Tables (Which are admittedly huge), it often gets to a
point where it just cant handle putting this Delta in and crashes. I am
often dragging hundreds of thousands of rows of data though from Access as
my source. The workaround I use it to make all the data for say 2003
negative and then use the normal Row totals as my delta field which puts no
extra strain on the system at all. Very quick and simple to do, so if the
first way doesn't work for any reason then post back.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

<[email protected]> wrote in message
news:[email protected]...
> Hello,
> I would like to add a column to a pivot table that would show the
> difference between the two other columns.
>
> Below is a sample of data and the pivot table I can get to.
>
> PrjType.........YR...........hrs
> 1...............2003............5
> 1...............2004............3
> 2...............2003............6
> 2...............2004............8
> 1...............2003............7
> 1...............2003............6
> 1...............2003............5
> 1...............2003............7
>
>
> .......................................____YR_______
> ProjType......Data...................2003.........2004
> 1.............Sum of hrs...............30...........3
> ..............Average of hrs2...........6...........3
> 2.............Sum of hrs............... 6...........8
> ..............Average of hrs2...........6...........8
>
> What I would like is to add a column ("Diff") to the right, after
> "2004" column to show the difference between 2004 and 2003-to
> look like
>
> ......................................___YR____
> ProjType......Data...................2003.....2004......Diff
> 1.............Sum of hrs...............30.......3.........-27
> ...............Average of hrs2.........6........3..........-3
> 2.............Sum of hrs............... 6.......8...........2
> ..............Average of hrs2..........6........8............2
>
> Thanks
>

Should have said - in the first method, you need to lose the Grand Totals
for the rows from the Table options, as they are nonsensical in this
context.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Ken Wright" <[email protected]> wrote in message
news:%[email protected]...
> You have a couple of ways of handling this. First you can insert a
> calculated item to handle this:-
>
> With your table as you had it, drag the project type into the ROW fields,
> the Year into the COLUMN fields, and the Hrs into the DATA field. Now
click
> on any of the years headings, ie 2003 or 2004, and then with the Pivot
table
> toolbar showing, click on Pivot Table / Formulas / Calculated item
>
> On the dialog box that appears, replace the 'Formula1' with some logical
> title such as 'Delta', and then with Year selected in the box on the left,
> in the box where it says ' Formula = 0', delete the 0, double click the
> 2004, type - and then double click the 2003 such that the formula now
looks
> like = '2004'- '2003'. Now just hit OK and it will be added.
>
> This works fine most of the time, but depending on how big your Pivot
table
> is, it may well bring it to it's knees. I use a laptop with a 1.7GHz
> Centrino processor and 1GB of RAM. That is a lot of processing power, and
> on some of my Pivot Tables (Which are admittedly huge), it often gets to a
> point where it just cant handle putting this Delta in and crashes. I am
> often dragging hundreds of thousands of rows of data though from Access as
> my source. The workaround I use it to make all the data for say 2003
> negative and then use the normal Row totals as my delta field which puts
no
> extra strain on the system at all. Very quick and simple to do, so if the
> first way doesn't work for any reason then post back.
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> --------------------------------------------------------------------------
--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
> <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> > I would like to add a column to a pivot table that would show the
> > difference between the two other columns.
> >
> > Below is a sample of data and the pivot table I can get to.
> >
> > PrjType.........YR...........hrs
> > 1...............2003............5
> > 1...............2004............3
> > 2...............2003............6
> > 2...............2004............8
> > 1...............2003............7
> > 1...............2003............6
> > 1...............2003............5
> > 1...............2003............7
> >
> >
> > .......................................____YR_______
> > ProjType......Data...................2003.........2004
> > 1.............Sum of hrs...............30...........3
> > ..............Average of hrs2...........6...........3
> > 2.............Sum of hrs............... 6...........8
> > ..............Average of hrs2...........6...........8
> >
> > What I would like is to add a column ("Diff") to the right, after
> > "2004" column to show the difference between 2004 and 2003-to
> > look like
> >
> > ......................................___YR____
> > ProjType......Data...................2003.....2004......Diff
> > 1.............Sum of hrs...............30.......3.........-27
> > ...............Average of hrs2.........6........3..........-3
> > 2.............Sum of hrs............... 6.......8...........2
> > ..............Average of hrs2..........6........8............2
> >
> > Thanks
> >
>
>

Tried tinkering around with the OP's pivot display replicated as a start
point earlier, viz,:

........................................____YR_______
ProjType......Data...................2003.........2004
1.............Sum of hrs...............30...........3
...............Average of hrs2...........6...........3
2.............Sum of hrs............... 6...........8
...............Average of hrs2...........6...........8

Right-clicking on say: "2003" > Formulas > Calculated Items resulted in an
error msg: "Multiple data fields of the same field are not supported when a
Pivot Table has calculated items" So it looked like a dead-end there ..

Followed your schema, got it (the calculated items bit) up with SUM (only),
but it failed when I tried similarly with AVERAGE, throwing up the error
msg: "Averages, std deviations and variances are not supported when a Pivot
Table has calculated items"

Above experimented in Excel 97. Not sure whether the limitations above apply
to later versions though ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
Ken Wright <[email protected]> wrote in message
news:[email protected]...
> Should have said - in the first method, you need to lose the Grand Totals
> for the rows from the Table options, as they are nonsensical in this
> context.
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> --------------------------------------------------------------------------
--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
> "Ken Wright" <[email protected]> wrote in message
> news:%[email protected]...
> > You have a couple of ways of handling this. First you can insert a
> > calculated item to handle this:-
> >
> > With your table as you had it, drag the project type into the ROW
fields,
> > the Year into the COLUMN fields, and the Hrs into the DATA field. Now
> click
> > on any of the years headings, ie 2003 or 2004, and then with the Pivot
> table
> > toolbar showing, click on Pivot Table / Formulas / Calculated item
> >
> > On the dialog box that appears, replace the 'Formula1' with some logical
> > title such as 'Delta', and then with Year selected in the box on the
left,
> > in the box where it says ' Formula = 0', delete the 0, double click the
> > 2004, type - and then double click the 2003 such that the formula now
> looks
> > like = '2004'- '2003'. Now just hit OK and it will be added.
> >
> > This works fine most of the time, but depending on how big your Pivot
> table
> > is, it may well bring it to it's knees. I use a laptop with a 1.7GHz
> > Centrino processor and 1GB of RAM. That is a lot of processing power,
and
> > on some of my Pivot Tables (Which are admittedly huge), it often gets to
a
> > point where it just cant handle putting this Delta in and crashes. I am
> > often dragging hundreds of thousands of rows of data though from Access
as
> > my source. The workaround I use it to make all the data for say 2003
> > negative and then use the normal Row totals as my delta field which puts
> no
> > extra strain on the system at all. Very quick and simple to do, so if
the
> > first way doesn't work for any reason then post back.
> >
> > --
> > Regards
> > Ken....................... Microsoft MVP - Excel
> > Sys Spec - Win XP Pro / XL 97/00/02/03
> >
>
> --------------------------------------------------------------------------
> --
> > It's easier to beg forgiveness than ask permission :-)
>
> --------------------------------------------------------------------------
> --
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > Hello,
> > > I would like to add a column to a pivot table that would show the
> > > difference between the two other columns.
> > >
> > > Below is a sample of data and the pivot table I can get to.
> > >
> > > PrjType.........YR...........hrs
> > > 1...............2003............5
> > > 1...............2004............3
> > > 2...............2003............6
> > > 2...............2004............8
> > > 1...............2003............7
> > > 1...............2003............6
> > > 1...............2003............5
> > > 1...............2003............7
> > >
> > >
> > > .......................................____YR_______
> > > ProjType......Data...................2003.........2004
> > > 1.............Sum of hrs...............30...........3
> > > ..............Average of hrs2...........6...........3
> > > 2.............Sum of hrs............... 6...........8
> > > ..............Average of hrs2...........6...........8
> > >
> > > What I would like is to add a column ("Diff") to the right, after
> > > "2004" column to show the difference between 2004 and 2003-to
> > > look like
> > >
> > > ......................................___YR____
> > > ProjType......Data...................2003.....2004......Diff
> > > 1.............Sum of hrs...............30.......3.........-27
> > > ...............Average of hrs2.........6........3..........-3
> > > 2.............Sum of hrs............... 6.......8...........2
> > > ..............Average of hrs2..........6........8............2
> > >
> > > Thanks
> > >
> >
> >
>
>

Hmmm - must admit I missed the bit with th two fields in there - Will take a
look and post back - Cheers for the catch Max.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

<snip>

Good pickup Max, same in current versions. Plan B then in my book :-)

Autofilter your data and filter on just 2003. Put -1 in an empty cell and
copy the cell. Select all the visible data (numerics only) and do edit / Go
To / Special / Visible cells only, then do Edit / Paste Special / Multiply.
Now just refresh your table. Not ideal because one set of data is all
negative, but you can now use the Grand Totals for rows as your delta field.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> Hmmm - must admit I missed the bit with th two fields in there - Will take
a
> look and post back - Cheers for the catch Max.
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> --------------------------------------------------------------------------
--
> It's easier to beg forgiveness than ask permission :-)
> --------------------------------------------------------------------------
--
>
> <snip>
>
>

Add another copy of the Hours field to the data area
Right-click on the heading for the new field
Choose Field Settings
Type a name for the field, e.g. Change
Click the Options button
For 'Show Data as', choose 'Difference From'
As the Base Field, choose Year
As the Base Item, choose (previous)
Click OK

[email protected] wrote:
> Hello,
> I would like to add a column to a pivot table that would show the
> difference between the two other columns.
>
> Below is a sample of data and the pivot table I can get to.
>
> PrjType.........YR...........hrs
> 1...............2003............5
> 1...............2004............3
> 2...............2003............6
> 2...............2004............8
> 1...............2003............7
> 1...............2003............6
> 1...............2003............5
> 1...............2003............7
>
>
> .......................................____YR_______
> ProjType......Data...................2003.........2004
> 1.............Sum of hrs...............30...........3
> ..............Average of hrs2...........6...........3
> 2.............Sum of hrs............... 6...........8
> ..............Average of hrs2...........6...........8
>
> What I would like is to add a column ("Diff") to the right, after
> "2004" column to show the difference between 2004 and 2003-to
> look like
>
> ......................................___YR____
> ProjType......Data...................2003.....2004......Diff
> 1.............Sum of hrs...............30.......3.........-27
> ...............Average of hrs2.........6........3..........-3
> 2.............Sum of hrs............... 6.......8...........2
> ..............Average of hrs2..........6........8............2
>
> Thanks
>


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

Ken Wright <[email protected]> wrote
> Good pickup Max, same in current versions. Plan B then in my book :-)
....
Thanks for the confirmation, Ken. At least I can rest easy on this <g>
I'm gonna leave it to the OP to try your plan B ..

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----