Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How do I subtract two columns of data in a pivot table?

    The column name is LE and the data is Sum of Amount...

    Thanks.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You did not mention the Excel version. FOr 2003 and below: click anywhere in the pivot table. Then on the pivot table toolbar, select PivotTable, Formulas, Calculated field. That dialog speaks for itself I think.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think I want to calculate an item because those fields don't come up as options. So, don't I want to do this instead of calculate a field??


    [quote name='pieterse' post='765026' date='12-Mar-2009 16:17']You did not mention the Excel version. FOr 2003 and below: click anywhere in the pivot table. Then on the pivot table toolbar, select PivotTable, Formulas, Calculated field. That dialog speaks for itself I think.[/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can't use the data fields of the pivot table in a calculated field.

    It might be helpful of you posted (a stripped down copy of) your workbook, with an indication of what you want.

  5. #5
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok. Here is a picture. I want to subtract the March LE data from the December LE data - column f from column e.


    [quote name='HansV' post='765034' date='12-Mar-2009 15:39']You can't use the data fields of the pivot table in a calculated field.

    It might be helpful of you posted (a stripped down copy of) your workbook, with an indication of what you want.[/quote]
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Note: I want to add the column w/the subtraction and title it variance...

    [quote name='HansV' post='765034' date='12-Mar-2009 15:39']You can't use the data fields of the pivot table in a calculated field.

    It might be helpful of you posted (a stripped down copy of) your workbook, with an indication of what you want.[/quote]

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Click in the pivot table.
    Start the Pivot Table Wizard.
    Click Layout...
    Add the Amount field to the Data area, since there is already an instance of Amount there, the new one will be called Sumof Amount2.
    Double-click the Sum of Amount2 button.
    Change the name if you like, then click Options>>.
    From the "Show data as" dropdown, select "Difference with".
    Select LE in the Base Field list.
    Select (previous) in the Base Item list.
    Click OK, OK, Finish.

  8. #8
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans, this didn't work... I am wanting to add an extra column to calculate this...

    Does this change your answer???

    Mitch

    [quote name='HansV' post='765049' date='12-Mar-2009 16:04']Click in the pivot table.
    Start the Pivot Table Wizard.
    Click Layout...
    Add the Amount field to the Data area, since there is already an instance of Amount there, the new one will be called Sumof Amount2.
    Double-click the Sum of Amount2 button.
    Change the name if you like, then click Options>>.
    From the "Show data as" dropdown, select "Difference with".
    Select LE in the Base Field list.
    Select (previous) in the Base Item list.
    Click OK, OK, Finish.[/quote]

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='mitjones' post='765082' date='12-Mar-2009 18:18']Does this change your answer???[/quote]
    Yes, in the sense that it can't be done in the pivot table as far as I know. You could add formulas to the right of the pivot table to calculate the difference, but they wouldn't be updated correctly if you changed the layout of the pivot table.

  10. #10
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans, it looks like I asked this same thing back on July 25, 2008. Below is your reply. I am trying a similar approach to this table, but it locks up my PC taking up all the CPU. I let it run for an hour and it was finished but didn't show a result...

    "Sorry, I interpreted your "I have two sets of columnar data" in a different way than you intended it. What you need is a calculated item instead of a calculated formula.
    - Click in one of the values of version.
    - From the Pivot Table toolbar, select Pivot Table | Formulas | Calculated Item...
    - Enter a name for the formula in the box at the top.
    - Enter the following formula in the formula box:

    =versions[2008 OP]-versions[Sept LE]

    - Make sure that you use the names exactly as they are in the data.
    - Click Add, then OK. "








    [quote name='HansV' post='765088' date='12-Mar-2009 17:32']Yes, in the sense that it can't be done in the pivot table as far as I know. You could add formulas to the right of the pivot table to calculate the difference, but they wouldn't be updated correctly if you changed the layout of the pivot table.[/quote]

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't know enough about your workbook.

  12. #12
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Attached is the file I'd like to do the math on...

    Mitch



    [quote name='HansV' post='765105' date='12-Mar-2009 18:01']I don't know enough about your workbook.[/quote]
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Click on Jan LE or Mar LE (or in one of the row fields)
    Select Pivot Table | Formulas | Calculated Item from the Pivot Table toolbar.
    Type a name for the field, e.g. Variance.
    Edit the formula so that it consists only of =
    Select Jan LE item in the list ot items, then click Add Item.
    Type - after it in the formula.
    Select Mar LE item in the list ot items, then click Add Item.
    The formula should now read ='Jan LE'-'Mar LE'
    Click Add, then click OK

    BTW you can reduce the size of the workbook significantly by basing it on the data table only instead of on entire columns.

    See the attached version.
    Attached Files Attached Files

  14. #14
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans, I was able to get it to work also on the smaller subset of data I posted. When I try to do it on the full data set, it never finishes and takes up 100% of the CPU for hours...

    I see your comment below, "BTW you can reduce the size of the workbook significantly by basing it on the data table only instead of on entire columns." Maybe this would fix the problem with the full data set.

    How do I do what you are referring to?

    Mitch


    [quote name='HansV' post='765137' date='12-Mar-2009 21:45']Click on Jan LE or Mar LE (or in one of the row fields)
    Select Pivot Table | Formulas | Calculated Item from the Pivot Table toolbar.
    Type a name for the field, e.g. Variance.
    Edit the formula so that it consists only of =
    Select Jan LE item in the list ot items, then click Add Item.
    Type - after it in the formula.
    Select Mar LE item in the list ot items, then click Add Item.
    The formula should now read ='Jan LE'-'Mar LE'
    Click Add, then click OK

    BTW you can reduce the size of the workbook significantly by basing it on the data table only instead of on entire columns.

    See the attached version.[/quote]

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='mitjones' post='765231' date='13-Mar-2009 14:27']How do I do what you are referring to?[/quote]
    Click anywhere in the pivot table.
    Select Pivot Table | Pivot Table Wizard from the Pivot Table toolbar.
    Click Back until you see the range the pivot table is based on. This range consists of entire columns. Clear this, and point to the data range.
    Finally, click Finish.

Posting Permissions

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