Results 1 to 10 of 10

Thread: Aging report

  1. #1
    3 Star Lounger
    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.
    Attached Files Attached Files

  2. 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!

    Excel 2013: The Missing Manual

    + 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!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,196
    Thanks
    14
    Thanked 326 Times in 320 Posts
    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

  4. #3
    3 Star Lounger
    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 take-a-way 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

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,196
    Thanks
    14
    Thanked 326 Times in 320 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 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

  6. #5
    3 Star Lounger
    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?

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,196
    Thanks
    14
    Thanked 326 Times in 320 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-$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

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,910
    Thanks
    190
    Thanked 719 Times in 655 Posts
    MNN,

    Maybe this will help.
    Formula Explained..JPG
    HTH

    Here's a Word version of the explanation.
    Formula Explained.doc
    Last edited by RetiredGeek; 2013-10-14 at 11:42.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  9. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,196
    Thanks
    14
    Thanked 326 Times in 320 Posts
    RetiredGeek,
    $F$3 is a "1", but the $I10 is a "3" not a "1"...

    Steve

  10. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,910
    Thanks
    190
    Thanked 719 Times in 655 Posts
    Steve,

    Sorry, A little sloppy with my drawing objects.
    Formula Explained..JPG
    Word Version
    Formula Explained.doc
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  11. #10
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    347
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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
  •