Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Pivot Table Ageing

    I have a Pivot Table and have tried to group as follows but cannot get it right

    < 30 days
    30-60
    61-90
    91-120
    > 120

    It would appreciated if someone could assist me on how to resolve the problem
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you want those exact ranges, you will have to create your own column and calculate. The grouping can not do this. You will not get those exact ranges for 2 reasons:
    The grouping is not regular: 30 - 60 has 31 numbers, but 61-90 and 91-120, have only 30.
    Excel grouping is all inclusive, can not ignore (as your listing indicates): >60 but <61, and >90 but <91. Excel wants to put them in one of the "bins"
    You can come close by setting the grouping to:
    Starting at: 31
    Ending at: 120
    by: 30

    This will give you close to the divisions you want:
    <31
    31-61 [>=31 and <61]
    61-91 [>=61 and <91]
    91-121 [>=91 and <121]
    >121 [Actually >=121]

    Or you could use
    Starting at: 30
    Ending at: 120
    by: 30

    This will give you:
    <30
    30-60 [>=30 and <60]
    60-90 [>=60 and <90]
    90-120 [>=90 and <120]
    >120 [Actually >=120]

    It depends on where you want the border values (>=30 but <=31,>=60 and <=61,>=90 but <=91,and >=120but <=121. A key point is regular bin sizes, the inclusion of all the values, the start of the bin is an equal, but the top value is only a less than (<)

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Have set to grouping to

    Starting at: 30
    Ending at: 120
    by: 30


    However, I am not getting the band 60-90 -see attached file

    Please check & advise

    Howard
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You don't have seem to have any data within that range in your dataset.

    Steve

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks Steve

    Did not check the raw data to determine whether there was data aged between 60 -90 days. it makes perfect sense

    Howard

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you always want them displayed, you can add some dummy data of each group.

    Steve

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the tip

    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
  •