1. ## 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

2. 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. 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

Howard

4. You don't have seem to have any data within that range in your dataset.

Steve

5. 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. If you always want them displayed, you can add some dummy data of each group.

Steve

7. 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
•