+ Reply to Thread
Results 1 to 9 of 9

To get the Most Recently paid EMI in tune with the current date.

  1. 02-11-2009, 02:07 PM #1
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    To get the Most Recently paid EMI in tune with the current date.

    Dear Forum,

    Most Recent Paid EMI Date:
    I need to get the Most Recently paid EMI in the List it should be in tune with the TODAY() as well as the PAID one indicated by "X" in Column F..


    Next EMI DATE:
    This would be the NEXT EMI date as the name suggests after the Most recent paid EMI…In this case 14-Feb-09


    Date to Replenish:
    This date would be when the Amount in the Account has got reduced indicated by "X" in column E...In this case 10-May-09.

    Any sugggestions to improve the concept would certainly be appreciated...
    Attached Files Attached Files
    Last edited by e4excel; 02-12-2009 at 08:47 AM.
    Reply With QuoteRegister To Reply
  2. 02-11-2009, 02:17 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: To get the Most Recently paid EMI in tune with the current date.

    Try these 3 formulas in cells:I4:I6, respectively.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    all to be confirmed with CTRL+SHIFT+ENTER instead of just ENTER
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 02-12-2009, 03:19 AM #3
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: To get the Most Recently paid EMI in tune with the current date.

    Thanks NBVC,

    Please Login or Register  to view this content.
    This formula does give me the correct answer for the DATE -10-Feb-09 but when I tried to get the corresponding "Name of the Debit" it gives me ICICI PRUD which is incorrect it should be RELIANCE II.

    Please Login or Register  to view this content.
    Can you please help me on the same...! and this is applicable for othe codes as well as Im interested in getting the corresponding Debit Names..

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    The answer here should be SUNDARAM and its correct 

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Here the answer should be FRANKLIN but the answer again is ICICI PRUD which is incorrect..

    Im sorry for not making a mention of the Debit Names in my requirement as I thought I could do it myselft using an INDEX function but it did not work..

    Sorry for that...
    Reply With QuoteRegister To Reply
  4. 02-12-2009, 04:51 AM #4
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: To get the Most Recently paid EMI in tune with the current date.

    Alternatives... which I think should work given data is sorted by date in Asc order... none of the below are arrays (so CTRL + SHIFT + ENTER not required)

    Please Login or Register  to view this content.
    Given repetition of TODAY() I would advise you store =TODAY() in a cell on your sheet and refer to that cell in the above rather than repeatedly calling the function from within the above.
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  5. 02-12-2009, 08:43 AM #5
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: To get the Most Recently paid EMI in tune with the current date.

    Hey thanks a lot DO,

    Given repetition of TODAY() I would advise you store =TODAY() in a cell on your sheet and refer to that cell in the above rather than repeatedly calling the function from within the above.
    I will be keeping the date dynamic anyways..So TODAY() will be store in a cell and will be called..But very good suggestion nevertheless...

    Yes you are absolutely right...
    J6 returns RELIANCE I not FRANKLIN...
    FRANKLIN being the first Debit on the day in which X occurs in E
    RELIANCE I is the first Debit containing an X in E.
    It was my folly in the concept and therefore I had asked for some improvisations in my first post..

    Any sugggestions to improve the concept would certainly be appreciated...
    Thanks for bringing this out and not only just helping in the code..
    You are brilliant..

    Thanks to NBVC and DonkeyOte both your codes were really very helpful...

    Dear DO,

    Can you please explain the code especially the coloured boldened portion ,,please...

    LOOKUP(2,1/(($B$1:$B$1000<=TODAY())*($F$1:$F$1000="X")),B$1:B$1000)

    Warm Regards

    e4excel.
    Last edited by e4excel; 02-12-2009 at 08:53 AM. Reason: Spelling Mistakes
    Reply With QuoteRegister To Reply
  6. 02-12-2009, 08:49 AM #6
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: To get the Most Recently paid EMI in tune with the current date.

    Both NBVC and myself answered a similar question here - posts 14 & 15:
    http://www.excelforum.com/excel-gene...eader-row.html
    Reply With QuoteRegister To Reply
  7. 02-12-2009, 09:05 AM #7
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: To get the Most Recently paid EMI in tune with the current date.

    Thanks for the link...

    Just one thing came to my notice while adding your formula..
    Some Anomaly..(Is it a deliberate one OR)

    [ =LOOKUP(2,1/(($B$1:$B$1000<=TODAY())*($F$1:$F$1000="X")),C$2:C$1001) ]

    The ROW number starts from C2 and ends till C1001...
    Any reasons for the same.?
    Reply With QuoteRegister To Reply
  8. 02-12-2009, 09:15 AM #8
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: To get the Most Recently paid EMI in tune with the current date.

    You have to ask ...

    Everything's deliberate...

    You'll see other varying references in some of the other formulae also...

    In the formula you refer to you're trying to find the next Debit after the last record up to / incl Today... you want to offset the result vector (2:1001) from the lookup vector (1:1000) by 1 row such that it finds the last record up to / incl today and returns the value from C in the row below.
    Reply With QuoteRegister To Reply
  9. 02-12-2009, 02:37 PM #9
    e4excel
    e4excel is offline
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: To get the Most Recently paid EMI in tune with the current date.

    Quote Originally Posted by DonkeyOte View Post
    You have to ask ...

    Everything's deliberate...

    You'll see other varying references in some of the other formulae also...

    In the formula you refer to you're trying to find the next Debit after the last record up to / incl Today... you want to offset the result vector (2:1001) from the lookup vector (1:1000) by 1 row such that it finds the last record up to / incl today and returns the value from C in the row below.
    Gotch u...

    I had to leave early as something had come up so I had not checked the other codes but you are right depending on the need you have offset the result vector..

    Thank you so much for the explanation it was very useful...
    Dont like to buy anything accidentally..U see
    So when I do try to replicate this logic in future I know exactly why to change the rows.. or else I would be but not now..Thanks to u

    Attachment Added for reference
    Attached Files Attached Files
    Last edited by e4excel; 02-12-2009 at 02:55 PM. Reason: Adding Attachment
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1