Ageing Buckets

Stephen Halsey

Hi All,

I have calculated the number of days between two points (Submission Date {Start Date} and Current Date {Today's Date} which has result in a numerical value being populated in a separate column called "Ageing". I have now added another column called Ageing Bucket and I need to replicate the rules below:

If Ageing is greater than 1 but less than or equal to 5 set to "1 - 5 days", or;

If Ageing is greater than 6 but less than or equal to 10 set to "6 - 10 days", or;

If Ageing is greater than 11 but less than or equal to 20 set to "11 - 20 days", or;

If Ageing is greater than 21 but less than or equal to 30 set to "21 - 30 days"; or.

If Ageing is greater than 31 set to "Greater Than 31 days".

Thanks in advance,

Steve

Summer

Hi @Stephen Halsey,

Here is one approach you can take. For this formula I put the expected return values in their own column. You can do the same, you can put them into your sheet summary and reference that, or you can continue to type in the value you want.

With formulas like this, I like to work "backwards" starting with the highest option, in this case equal to or greater than 31 and moving back towards the lowest option of 1-5. This allows for the order of operations to follow the logic of "Is it higher than this?" No, okay then "Is it higher than this" and so on.

=IF(Ageing@row >= 31, $[Key Values]$5, IF(Ageing@row >= 21, $[Key Values]$4, IF(Ageing@row >= 11, $[Key Values]$3, IF(Ageing@row >= 6, $[Key Values]$2, IF(Ageing@row >= 1, $[Key Values]$1, "")))))

Here are screenshots showing my example sheet and how the Aging Bucket column result changes depending on the aging bucket.

I hope this helps you get unstuck.