Results 1 to 4 of 4

Thread: calculating differences

  1. #1
    Neophyte mbrewer's Avatar
    Join Date
    Jan 2013
    Location
    Prince George, BC
    Posts
    2
    Articles
    0

    calculating differences



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi...I have a pivot table where I have grouped the dates into years (columns) and customers (rows) and I'm trying to add a calculated field that shows the difference between 2012 and 2013...can it be done? I've searched hi and low and can't seem to find anything on how to do this.

    thanks!

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Easiest way would be to create a 2012 and a 2012 column in your data source, then pull those in individually. At that point it's fairly easy to create a filed to get the difference.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Neophyte mbrewer's Avatar
    Join Date
    Jan 2013
    Location
    Prince George, BC
    Posts
    2
    Articles
    0
    was hoping you weren't going to say that :-) lol thanks ... back to the drawing board

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,225
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I've never had any real succes with trying to extract multiple items from a single column. You can use an IF statement in your calc columns, so this would work, but it's going to be hokey:

    2013: =IF(DateField>41274,Amount,0)
    2012to2013: =IF(DateField>40908,Amount,0)
    2012: 2013-2012to2013

    I've never been able to nest anything beyond a simple IF though. So that might get you there, it just won't be elegant.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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