Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Dec 2001
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Using Excel 2007:

    Have a pivot table in a sheet. Column A has item numbers, column B has value data from November, Column C has value data from December. Doing the Pivot Table, when dragging the "extended cost" field to the values section - it populates the data for the months and then creates a new "grand total" column totalling the two month columns for each item #.

    However - I want the Grand Total column to not show the total, but the difference between the two columns, from November to December. Trying to use the Difference From calculation doesn't work - and it causes it to populate the difference in the December column.

    Suggestions? Do I need to do a custom calculation and if so, how would I do it?

    --*Rob

  2. #2
    New Lounger
    Join Date
    Dec 2009
    Location
    Seattle, WA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The simplest way to accomplish this is to code your calculation in another column, outside the bounds of the pivot table. For example, if your table occupies columns A, B, and C, and if row 3 contains the first detail line of your pivot table, you can calculate the difference between C3 and B3, simply by coding the formula: =C3-B3 in D3 (or any other unused column to the right of the the pivot table)

    You can copy this fomula down the D column to accomplish this for every line of your pivot table.

    The only drawback to this methd is that if you change the number of columns in the actual pivot table, you have to manually move the calculations. Since the formua lies outside the pivot table, and uses absolute row & column addresses, it does not change in sync with all the pivot table changes.

    But for a simple, one time application, this is a quick solution. And it can be modified easily if the pivot table must change. I use this technique myself with some tables that are updated daily, but the number of columns rarely changes. Outside the dimensions of the pivot table the rest of the worksheet the table resides in is still available for use. And you can even reference the contents of the pivot table in this "outside" area (or in any other worksheet for that matter). you just need to be aware that changes to the pivot table can change, delete, or overlay the data and calculations in this "outside" area.

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi robaustin - Is it possible to post a sample of what has been done so far. Sounds like a Calculated Field may be needed.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts
    in another program I use, to do the subtraction in a pivot table you would use the following calculated field

    =([Value] Where ([Month]=Nov)-[Value] Where ([Month]=Dec))

    I've never tried it in excel, but would presume it would be similar.
    Excel doesn't have where so you might have to use an IF statement instead

    capri

Posting Permissions

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