Finding date of last payment in excel sheet

[dloskot]

I am on the board of charity that does micro loans in Africa and we used excel sheet to keep track of loan repayments. I would like to find the date of the last payment. Column A has the date and Column C has the payment. All the payment cells are pre filled with zeros. I think a CSE formula could be used but I have not been able to find the right formula to do this. I am hoping some here can help me write the correct formula. Below is an example of that a repayment sheet would look like. In this example I would like to have a formula that would return the date of 23-Dec-14 as that was the date of the last payment as shown in Column C.

A B C D E F G H
1 Payment Date Weekly Amt Payment Past Due Paid to Date Loan Balance Savings Saving Bal
2 2-Dec-14 19,300 38,600 0 38,600 461,400 0 0
3 9-Dec-14 19,300 10,000 9,300 48,600 451,400 0 0
4 16-Dec-14 19,300 0 28,600 48,600 451,400 0 0
5 23-Dec-14 19,300 15,000 47,900 48,600 451,400 0 0
6 30-Dec-14 19,300 0 67,200 48,600 451,400 0 0

I hope someone can help me with this.

Doug

 

[Aladin Akyurek]

Aladin: BINGO!!!!! That works. I don't understand what is different but I just tried it on 10 sheets that were not working before and now they all calculate the correct date. Thank you so much. I know you spent more time on it that I am sure you wanted but it works and that is great. I and the Charity greatly appreciate it.
If you have time and are willing could you explain how it works. It looks like the first formula determines the last row is row 56, but I don't understand how the second formula finds the last payment?
[...]

As long as column A has nothing else but dates, vaild data.

00. The first formula determines the last row of the data.

01. The second formula looks generically like this:

LOOKUP(BigNum,Reference1,Reference2)

and seeks the last numeric value in Reference1 and returns the value from Reference2 that corresponds positionwise with last numeric value of Reference1.

02. The INDEX bits that the second formula work like this:

start cell : INDEX(column of the start cell, last row)

which creates a reference that goes from the start cell to the last row.

03. BigNum is the largest number that can be type into an Excel cell.

Here are some relevant links:
http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html

http://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html#post492425 [Note. 2 of this links is simply a variant of BigNum ]
http://www.mrexcel.com/forum/excel-...tiple-matches-match-returned.html#post1523998


Hope this helps.
<strike></strike>

 

[dloskot]

I will have to study this a little to fully understand it completely but it gives me something else to do. Thanks for all your help. I have modified all the repayment sheet we have in the charity and your formula worked like a charm.
Again thanks for all the help, there is no way I would have figured this out on my one.
Doug

 

[Aladin Akyurek]

I will have to study this a little to fully understand it completely but it gives me something else to do. Thanks for all your help. I have modified all the repayment sheet we have in the charity and your formula worked like a charm.
Again thanks for all the help, there is no way I would have figured this out on my one.
Doug

You are welcome.