Subtracting Pivot Table Columns (2003)

Topic

New Reply

Hi,

How do I subtract columns in a pivot table? I have one field called months on the drop cloumn field. Within months are Sep and Aug and Grand Total. What I am trying to do is subtract Aug from Sep and show the variance in a column (preferably replacing the Grand Total). I want to do this without copy/paste special the values on a another tab and subtreacting the difference.

Thank you,
aluis

Reply | Quote

Replies

You can change the way the data field is displayed: double click the grey field button and click Options >> in the dialog that appears. You can select ‘Difference from’ and then (Previous).

But this means that the data for August and September will not be shown any more, only the difference.

You can also add a calculated column to the right of the pivot table, with formulas that subtract the Sep values from those for Aug.

Reply | Quote

Hi Hans,

I figure out your first suggestion but that was after I could not get your second option (add a calculated column to the right of the pivot table, with formulas that subtract the Sep values from those for Aug) to work on my own.

Can you help me with the formula? The last time I did it I added the values in column B and then subtracted column C, but the problem was that all the results were the sams from the first row to the last.

See sample below:

=+GETPIVOTDATA(“New Products”,A3,”Month”,”AUG”,”Project Name”,”US_AGD CM i-Whole Loans”)-GETPIVOTDATA(“New Products”,A3,”Month”,”SEP”,”Project Name”,”US_AGD CM i-Whole Loans”)
=+GETPIVOTDATA(“New Products”,A4,”Month”,”AUG”,”Project Name”,”US_AGD CM i-Whole Loans”)-GETPIVOTDATA(“New Products”,A4,”Month”,”SEP”,”Project Name”,”US_AGD CM i-Whole Loans”)
=+GETPIVOTDATA(“New Products”,A5,”Month”,”AUG”,”Project Name”,”US_AGD CM i-Whole Loans”)-GETPIVOTDATA(“New Products”,A5,”Month”,”SEP”,”Project Name”,”US_AGD CM i-Whole Loans”)
=+GETPIVOTDATA(“New Products”,A6,”Month”,”AUG”,”Project Name”,”US_AGD CM i-Whole Loans”)-GETPIVOTDATA(“New Products”,A6,”Month”,”SEP”,”Project Name”,”US_AGD CM i-Whole Loans”)

Reply | Quote

In this situation, I’d probably type the cell references in the formula manually instead of clicking on the cells. Say that the August data are in P4:P… and the September data in Q4:Q…
In T4 (or wherever you want it), enter the formula =Q4-P4, and fill down as far as needed.

Reply | Quote

Thank you Hans. That works perfectly.

Regards,
Aluis

Reply | Quote