Results 1 to 6 of 6

Thread: Finding values for specific date

  1. #1

    Question Finding values for specific date



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have following values:
    (1) Loan agr. start date (col. A)
    (2) Loan agr. end date (col. B)
    (3) Loan amount (col. C)

    And I have to find the amount of balance outstanding as at particular date.
    Example:
    1. 08/11/2013 - 08/13/2013 3500 USD
    2. 08/12/2013 - 08/15/2013 5000 USD
    3. 08/14/2013 - 08/15/2013 1000 USD

    So, in 08/13/2013 the balance outstanding was 8500 USD. Could anyone help with formulas?

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Possibly:

    =SUMIF(A1:A10,"<="&X1,C1:C10)

    where X1 contains the date to check.


  3. #3

    Arrow

    Quote Originally Posted by NBVC View Post
    Possibly:

    =SUMIF(A1:A10,"<="&X1,C1:C10)

    where X1 contains the date to check.
    Nope... doesn't work. I thought that solution could be find in INDEX/MATCH formulas... but I am not an expert in these...

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    Can you post a sample of the data in a spreadsheet attachment?


  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    How about

    =SUMIFS(C1:C3,A1:A3,"<=" & X1,B1:B3,">=" & X1)

    where X1 is the date to check

    I'm using Excel 2010

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    I presume that the formula is to operate on the assumption that the loans are all paid off on the agreement end date, in which case the NoS one gives the correct figure. In reality, the repayments could be early or late, meaning that you need a repayment date for an accurate balance ?

Posting Permissions

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