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

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - add a column to a pivot table that would show the difference between 2 other Columns (https://www.excelbanter.com/excel-worksheet-functions/10651-add-column-pivot-table-would-show-difference-between-2-other-columns.html)

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 :-)
----------------------------------------------------------------------------

wrote in message
oups.com...
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" wrote in message
...
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 :-)
--------------------------------------------------------------------------
--

wrote in message
oups.com...
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 <atyahoo<dotcom
----
Ken Wright wrote in message
...
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" wrote in message
...
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 :-)

--------------------------------------------------------------------------
--

wrote in message
oups.com...
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" wrote in message
...
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

Ken Wright 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 <atyahoo<dotcom
----