Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    975
    Thanks
    63
    Thanked 2 Times in 2 Posts

    Pivot table grouping

    I have set up a Pivot table where I have grouped the days in multiples of 5.

    < 5 or blank
    5-10
    10-15
    15-20
    20-25
    25-30
    >30

    I would like to know how I can go about changing this to

    > 5
    5-10
    11-15
    16-20
    21-25
    26-30
    >30

    I have attached my sample data

    Your assistance in this regard is most appreciated
    Attached Files Attached Files
    Last edited by HowardC; 2014-06-08 at 11:24.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,902
    Thanks
    190
    Thanked 719 Times in 655 Posts
    Howard,

    I don't think you can since Greater than 5 and all the other categories would overlap thus either making it impossible for Excel to calculate the pivot or double reporting. HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #3
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    975
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi RG

    Thanks for the reply. I made a type the > 5 should be < 5.

    Howard

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,902
    Thanks
    190
    Thanked 719 Times in 655 Posts
    Howard,

    The PT in your example is NOT based on the Imported Data tab as it does not contain the Cash Sale Value field.
    howard.JPG Please post an example with the appropriate data.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,194
    Thanks
    14
    Thanked 325 Times in 319 Posts
    RG, the "Cash Sale Value" is the Custom name he assigned to the "Outstanding" field in the data...

    The question seems to be how to remove the " or (blank)" that Excel defaults to when grouping numbers in a pivot table. If you select Cell A4, you can edit it to remove the " or (blank)"


    Steve
    Last edited by sdckapr; 2014-06-08 at 17:45.

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,902
    Thanks
    190
    Thanked 719 Times in 655 Posts
    Quote Originally Posted by sdckapr View Post
    RG, the "Cash Sale Value" is the Custom name he assigned to the "Outstanding" field in the data...Steve
    Steve,

    Right you are I missed that completely!

    However, I still get the message that the data was not saved?
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,194
    Thanks
    14
    Thanked 325 Times in 319 Posts
    I don't have that issue with it. it opens fine for me...

    Steve

  9. #8
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    975
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Steve & RG

    Thanks for the replies. I have edited A4 and changed this to <5

    How do I go about changing the range of the grouping so that the next range increases by 1?

    for e.g. original grouping <5, 5-10 , 10-15, 15-20 , 20-25, > 30 change to < 5, 5-10, 11-15, 16-20, 21-25, 26-30 , > 30

    Howard

  10. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,194
    Thanks
    14
    Thanked 325 Times in 319 Posts
    The shorthand they use should more properly be written
    5-10 means values >=5 and <10
    10-15 means values >=10 and <15
    etc

    you can edit the text, but it would be reflect the groupings better if you used: something more like:
    5-9, 10-14, 15-19, etc or if your numbers are not whole numbers:
    5-9.9, 10-14.9, 15-19.9, etc [add as many decimals as you like]

    Your edit suggests that a value of 10 will fall in the 5-10 box and it will not, it is in the 10-15 box

    Steve

  11. #10
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    975
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Steve

    Thanks for the explanation, much appreciated

    Howard

Posting Permissions

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