Formula to calculate aging buckets?

[General Ledger]

Dear All,

I am looking for a formula that will calculate aging buckets for transactions. How do you translate the number of days into age groups such as: 0-30, 31-60, 61-90, 91-120, and >120.

Once you have that figured out, let's push the limits. How about groups like: 0-30, 31-60, 61-90, 91-180, 181-365, >1 Year, >2 Years, and >3 Years.

Thank you very much,

GL

 

[shg]

=lookup(age, {0,31,61,...}, {"0-30","31-60","61-90",...})

 

[General Ledger]

shg,

OMG!! So Elegant! So Beautiful! This is a million dollar solution.

Thank you,

GL

 

[shg]

You're welcome.

 

[DataWrangler]

shg,

OMG!! So Elegant! So Beautiful! This is a million dollar solution.

Thank you,

GL

Exactly. Wow, this is just what I needed......and smartly done. I wouldn't have thought of a lookup vector in a million years.

 

[ujha1812]

This is amazing.
Just checking if we need to add a bucket 'Less than 0', can it be done in this formula.

Thanks

 

[Alex Blakenburg]

This is amazing.
Just checking if we need to add a bucket 'Less than 0', can it be done in this formula.

Thanks

Try something like:

=LOOKUP(age, {-9999,0,31,61,91}, {"Not Due","0-30","31-60","61-90",">=91"})

 

[ujha1812]

Wow...Thank you.. Much appreciated..

 

[Alex Blakenburg]

You're welcome