Hi every one... Can you help me in making formula of my Aging Report. I attached already the file for editing. Any kind of effort is very much appreciated. Thank you so much. More power to this site..
Hi every one... Can you help me in making formula of my Aging Report. I attached already the file for editing. Any kind of effort is very much appreciated. Thank you so much. More power to this site..
Last edited by relats; 03-09-2011 at 04:31 AM.
could you be more specific in what you need so we can input the correct formula
Actually I want the amount will be aged per column according to 0-30 days, 31-60 days 61-90 and so forth. Additionally, due amount beyond 365 days will be aged per month like 13-80 months and so forth. Furthermore, number days are already calculated in column F.
Herewith re-attached file as sample. I put the amount in column according to aging manually. I just want to make it in formula so that it would hugely lessen my manual work. Thank you so much my friend.
You can put this in G4 and copy to G13 and drag over to col J
=IF(AND($F4>(COLUMNS($G:G)-1)*30,$F4<COLUMNS($G:G)*30+1),$E4,"")
In K4 put this and drag over to M4 then copy down to row 13
=IF(AND($F4>LEFT(K$3,3)-1,$F4<RIGHT(K$3,3)+1),$E4,"")
I'm leaving the last 3 columns (months) to you so you can decide what # of days to assign. ie how many days for 18 months?
In N4 and copied down you would have:
=IF(AND($E$3>=EDATE($C4,12),$E$3<=EDATE($C4,18)),$E4,"")
In O4 and copied down
=IF(AND($E$3>EDATE($C4,18),$E$3<=EDATE($C4,24)),$E4,"")
and in P4 and copied down
=IF($E$3>EDATE($C4,24),$E4,"")
Last edited by Cutter; 03-07-2011 at 11:33 AM.
Oh what a wonderful endeavor you had been to me. My problem is already solved 99.9% but it is actually enough for me. But if you need a revisions regarding months this is it 18months = 546days, 24months =730 days and 24months =731 days and above. Thank you for the sharing knowledge with my sincere appreciation for your efforts. May God bless this site and especially to you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks