required excel formula of ageing analysis

User avatar
PRADEEPB270
3StarLounger
Posts: 354
Joined: 27 Oct 2013, 15:11
Location: Gurgaon INDIA

required excel formula of ageing analysis

Post by PRADEEPB270 »

please refer my attachment.



please suggest an excel formula in column AA,as i have type on this column.
You do not have the required permissions to view the files attached to this post.
Regards

Pradeep Kumar Gupta
INDIA

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: required excel formula of ageing analysis

Post by HansV »

See the attached version. I used VLOOKUP in combination with a lookup table.

FBL1N-30.06.2021 - Copy.xlsb
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
PRADEEPB270
3StarLounger
Posts: 354
Joined: 27 Oct 2013, 15:11
Location: Gurgaon INDIA

Re: required excel formula of ageing analysis

Post by PRADEEPB270 »

Thanks Hans Sir.

It is working as desired.
Regards

Pradeep Kumar Gupta
INDIA

Toranaga
3StarLounger
Posts: 254
Joined: 15 Aug 2016, 11:23

Re: required excel formula of ageing analysis

Post by Toranaga »

Or you can use this formula:

=LOOKUP(Z5,{0,46,91,181,366,1099},{"0-45","46-90","91-180","6 months to 1 year","more then 1 year","more then 3 years"})