1. ## 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.

2. In J10:
=IF(\$F\$3<\$I10,\$H10,"")

In K10
=IF(INT((\$F\$3-\$I10-1)/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

3. 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 take-a-way day..

second, it divides by 30. what is this purpose?

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

4. 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 0-30, 30-60, 60-90, etc. The "0-bucket" (0-30) is in column k which is the 11th column, the 1-bucket (30-60) 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

5. 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?

6. 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-\$I10-1)/30)+11 = 11

or when (INT((\$F\$3-\$I10-1)/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-\$I10-1)/30)+11 = 12

or when (INT((\$F\$3-\$I10-1)/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-\$I10-1)/30)=COLUMN(K1)-11,\$H10,"")

or more generally:
=IF(INT((\$F\$3-\$I10-1)/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-\$I10-1)/30)=COLUMN()-11,\$H10,"")
or
=IF(INT((\$F\$3-\$I10-1)/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

7. MNN,

Maybe this will help.
Formula Explained..JPG
HTH

Here's a Word version of the explanation.
Formula Explained.doc

8. RetiredGeek,
\$F\$3 is a "1", but the \$I10 is a "3" not a "1"...

Steve

9. Steve,

Sorry, A little sloppy with my drawing objects.
Formula Explained..JPG
Word Version
Formula Explained.doc

10. Thank you

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•