Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subtracting Pivot Table Columns (2003)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subtracting Pivot Table Columns (2003)

    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.

  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtracting Pivot Table Columns (2003)

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subtracting Pivot Table Columns (2003)

    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.

  5. #5
    Star Lounger
    Join Date
    Oct 2003
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subtracting Pivot Table Columns (2003)

    Thank you Hans. That works perfectly.

    Regards,
    Aluis

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •