Results 1 to 10 of 10
Thread: Aging report

20131010, 09:11 #1
 Join Date
 Jun 2005
 Posts
 371
 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.

20131010, 10:27 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 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, 02:06 #3
 Join Date
 Jun 2005
 Posts
 371
 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, 08:10 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 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, 12:07 #5
 Join Date
 Jun 2005
 Posts
 371
 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, 12:39 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 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, 12:40 #7
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 8,171
 Thanks
 296
 Thanked 1,169 Times in 1,068 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 12:42.

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

20131014, 12:53 #9
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 8,171
 Thanks
 296
 Thanked 1,169 Times in 1,068 Posts
Steve,
Sorry, A little sloppy with my drawing objects.
Formula Explained..JPG
Word Version
Formula Explained.doc

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