Hello,
I am posting here for the first time. Thanks for inviting me. My problem: in the attached excel sheet I want a formula which will calculate the amount of loan which is overdue. I have elaborated more in the attached sheet.
You might find the approach that I have used in the following formula useful. But the formula is not working: =IF(AND(TODAY()>EDATE(D6,MAX(INDEX((H$6:H6)*(C$6:C6=C6)*(E$6:E6="loan"),)))),LOOKUP(2,1/((E$6:E6="repayment")*(C$6:C6=C6)),G$6:G6),"")
Let me beef my question up, following some of suggestions made by Variatus (received with appreciation. I am working on other suggestions:
- Yes it is unrealistic for someone to make a deposit (savings) and take a loan on the same date. I have attached a sheet in which dates are different.
- In the original excel sheet which I am already using to administor a small serving and credit scheme, I have used data validation to restrict someone from taking a loan while they have an outstanding loan. Hence the overdue loan should be the last loan taken by that person.
- Variatus you have suggested that I have designed this sheet in a way which makes it difficult to calculate outstanding loan without VBA. I thank you in advance on ideas on redesigining the sheet.
- Please note that my final problem has not been solved yet
Again thank you Variatus and other people who are working on this. I am an excel novice. Your ideas and support are very encouraging and highly appreciated.