Results 1 to 10 of 10
Thread: Aging report

20131010, 08:11 #1
 Join Date
 Jun 2005
 Posts
 347
 Thanks
 3
 Thanked 0 Times in 0 Posts
Aging report
In the attachment below, there is a worksheet that demonstrates a report that I would like to prepare. I am not sure of the formulas for capturing the "INV AMT" in column H and allocate based on the number of days from the "DUE DATE" column I to the report date in cell F3.
The issue is the formula to grab the INV AMT into the correct ageing bucket.
Thank you.

Subscribe to our Windows Secrets Newsletter  It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual  Subscribe and download Chapter 1 for free!

20131010, 09:27 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,162
 Thanks
 14
 Thanked 317 Times in 311 Posts
In J10:
=IF($F$3<$I10,$H10,"")
In K10
=IF(INT(($F$3$I101)/30)+11=COLUMN(K1),$H10,"")
Copy K10 to L10:P10
In Q10:
=IF($F$3$I10>180,$H10,"")
Copy J10:Q10 down the columns
Steve

20131011, 01:06 #3
 Join Date
 Jun 2005
 Posts
 347
 Thanks
 3
 Thanked 0 Times in 0 Posts
The formula works however, I can't figure out how it works.
First it obtain the # of days between the due date and the report date and subtracts 1, why subtract 1, why not add 1 to account for the takeaway day..
second, it divides by 30. what is this purpose?
Third, Adding 11, Why add anything and especially 11?
Fourth, There is nothing in row 1 so why is that the value of the cell(s) to equal? Additionally it is not anchored to row 1 and will move down the worksheet relatively with the table.
Thank you

20131011, 07:10 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,162
 Thanks
 14
 Thanked 317 Times in 311 Posts
1) Since you had >180 and not >=180, I presumed the upper limit was an "equal to". This required the difference to have the "1" [But maybe my logic is wrong, I did test some. Have you looked at the days near the transitions and found bad placements?] My presumption is that the buckets were Min < x =< Max
2) The 30 comes from your data "buckets" each is 30 days...
3) The 11 comes from columns. I am comparing the column you are versus an offset. The Integer of the (day /30) gives values of 0, 1, 2, 3, etc for 030, 3060, 6090, etc. The "0bucket" (030) is in column k which is the 11th column, the 1bucket (3060) is in the 12th column, etc
4) The row number has no meaning. I could have chosen any row. I wanted the column number. [I could have used just Column(), to signify the column it was in, but in my testing, I created the formulas in blank columns to compare to your results.
Steve

20131014, 11:07 #5
 Join Date
 Jun 2005
 Posts
 347
 Thanks
 3
 Thanked 0 Times in 0 Posts
I understand the logic however, I do not understand how column K = 11 since there is no value or formula telling the overall formula that it equials the number ll. I do not see tha offset function formula either. So how does EXCEL identifies the COLUMNS K  O as 11 so the first part of the formula can test against it?

20131014, 11:39 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,162
 Thanks
 14
 Thanked 317 Times in 311 Posts
Excel only identifies Column K as 11, Column L is 12, Column M is 13, etc.
COLUMN(K1)[=11] is in formula of the cells for Column K and identifies that column as 11. This is the zeroth column is filled in when
(INT(($F$3$I101)/30)+11 = 11
or when (INT(($F$3$I101)/30)= 0
When copied, it will be column(L1)[=12] in the formulas for Column L and identifies that column as 12. This is the 1st column is filled in when
(INT(($F$3$I101)/30)+11 = 12
or when (INT(($F$3$I101)/30)= 1
Columns M (=13), N (=14), and O (=15) are the 2nd, 3rd, and 4th columns, respectively, in the range...
Would a formula in K10 like:
=IF(INT(($F$3$I101)/30)=COLUMN(K1)11,$H10,"")
or more generally:
=IF(INT(($F$3$I101)/30)=COLUMN(K1)COLUMN($K$1),$H10,"")
be more intuitive to you? They are equivalent. Column(K1) represents the column you are in, Column($K$1) the "zeroth Column" of the range
You could also use in K10
=IF(INT(($F$3$I101)/30)=COLUMN()11,$H10,"")
or
=IF(INT(($F$3$I101)/30)=COLUMN()COLUMN($K$1),$H10,"")
[As I mentioned earlier, I used Column(K1) instead of Column() since I was not putting the formula in K10...]
Steve

20131014, 11:40 #7
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 5,826
 Thanks
 185
 Thanked 704 Times in 642 Posts
MNN,
Maybe this will help.
Formula Explained..JPG
HTH
Here's a Word version of the explanation.
Formula Explained.docLast edited by RetiredGeek; 20131014 at 11:42.

20131014, 11:43 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,162
 Thanks
 14
 Thanked 317 Times in 311 Posts
RetiredGeek,
$F$3 is a "1", but the $I10 is a "3" not a "1"...
Steve

20131014, 11:53 #9
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 5,826
 Thanks
 185
 Thanked 704 Times in 642 Posts
Steve,
Sorry, A little sloppy with my drawing objects.
Formula Explained..JPG
Word Version
Formula Explained.doc

20131020, 20:24 #10
 Join Date
 Jun 2005
 Posts
 347
 Thanks
 3
 Thanked 0 Times in 0 Posts
Thank you